0 Replies Latest reply: Aug 14, 2017 2:52 AM by Supriya R RSS

    lookup table by combining records from different table for different rows.

    Supriya R

      I have a requirement where i need to create lookup table by combining records from two different table like below shown

       

      If  seller_id   > 1 for single fruit_id, then i have to get records data from commonFruitTable else if  seller_id   > 1 for single fruit_id, then get data from  RareFruitTable.

       

      MasterTable:

      fruit_id      fruit_type            fruit_name   seller_id   seller_camp_id

      1             new_arrival            orange         123             1

      1             stocked                   mango         124             2

      1             stocked                   mango         125             1

      2             new_arrival              mango        126             3

      3             stocked                   mango         127             4

      4             new_arrival             mango         128             5

      5             stocked                   mango         129             6

      5             new_arrival             mango         130             6

      6             stocked                   mango         131             7

      6             new_arrival            mango          132             8

      6             stocked                   mango         133             9

       

      commonFruitTable: (This table contains only data where seller_id > 1, for single fruit_id)

      seller_id   fruit_advertised  fruit_stats

      123              5                          34

      124              6                          35

      125              7                          2

      129              8                          3

      130              9                          5

      131              10                        8

      132              11                        89  

      133              12                         1

       

      RareFruitTable:  (This table contains both data where seller_id > 1, for single fruit_id and seller_id = 1, for single fruit_id)

      seller_camp_id   fruit_advertised        fruit_stats

      3                              1                                  0

      4                               2                                    1

      5                              1                                    0

      1                              5                                   34

      2                              6                                   35

      9                             12                                   1

      8                             11                                   89

       

      ActualMasterTable:

      fruit_id      fruit_type            fruit_name   seller_id   seller_camp_id   fruit_stats

      1             new_arrival            orange         123             1                       34

      1             stocked                   mango         124             2                       35

      1             stocked                   mango         125             1                        2

      2             new_arrival              mango        126             3                        0

      3             stocked                   mango         127             4                        1

      4             new_arrival             mango         128             5                        0

      5             stocked                   mango         129             6                        3

      5             new_arrival             mango         130             6                        5

      6             stocked                   mango         131             7                        8 

      6             new_arrival            mango          132             8                        89

      6             stocked                   mango         133             9                         1



      How could i merge different table to different records based on count of seller_id. Please help me on this. and Let me know if you need more clarification on this if i am not clear