MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Data fetch from One to Many relation table

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
kavi
Beginner


Joined: 15 Sep 2006
Posts: 64
Topics: 22
PostPosted: Tue Nov 05, 2013 8:10 am    Post subject: Data fetch from One to Many relation table Reply with quote
Hi Everyone,

I have three tables as follows

1. Master_Product,
Code:

Product_ID   - PK
Product_Category   - PK   
Prod_ type
Region_type


Product_ID   Product_Category   Prod_type   Region_type
0001           1234                A            AA
0002           1234                B            RR


2. Other_ LOB (One to One)
Code:

Product_ID   - FK
Product_Category   - FK   
Discount_apply
Bulk_apply

Product_ID   Product_Category   Discount_apply   Bulk_apply
0001             1234                  Y             N
0002             1234                  N             Y


3. Network (One to many with Master)

Code:

Product_ID      - FK
Product_Category   - FK
Sub_Product

Product_ID   Product_Category   Sub_Product   
0001              1234          9876
0001              1234          9875
0002              1234          9876
0002              1234          9875


Need to declare a Cursor to fetch the row joining all three tables and my o/p would be for the product ID 0001 and category 1234
Code:


Product_ID Product_Category Prod_ type Region_type Discount_apply   Bulk_apply Sub_Prodcut Sub_Prodcut
0001       1234              A         AA              Y                  N              9876      9875   



Could some help me to achive this.

Thanks in advance, Kavi
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Nov 05, 2013 10:17 am    Post subject: Reply with quote
Kavi,

What happens if you have more than 2 rows for a single key in the Network table?

ie.

Code:

Product_ID   Product_Category   Sub_Product   
0001              1234          9870
0001              1234          9871
0001              1234          9872
0001              1234          9873
0001              1234          9874
0001              1234          9875
....
0001              1234          99999


How would you fit all these values into a single row?
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kavi
Beginner


Joined: 15 Sep 2006
Posts: 64
Topics: 22
PostPosted: Tue Nov 05, 2013 12:24 pm    Post subject: Reply with quote
Hi Kolusu,

Network table can have maximum 10 rows for single key, and all available value should be fetched.

Thanks, Kavi
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Nov 05, 2013 12:40 pm    Post subject: Reply with quote
Kavi,

Try this

Code:

SELECT A.PRODUCT_ID     
      ,A.PRODUCT_CATEGORY
      ,A.PROD_TYPE       
      ,A.REGION_TYPE     
      ,B.DISCOUNT_APPLY 
      ,B.BULK_APPLY     
      ,C.SUB_PRODUCT01   
      ,C.SUB_PRODUCT02   
      ,C.SUB_PRODUCT03   
      ,C.SUB_PRODUCT04   
      ,C.SUB_PRODUCT05   
      ,C.SUB_PRODUCT06   
      ,C.SUB_PRODUCT07   
      ,C.SUB_PRODUCT08   
      ,C.SUB_PRODUCT09   
      ,C.SUB_PRODUCT10                                             
  FROM Master_Product    A                                                 
      ,Other_ LOB        B                                                 
      ,(SELECT Z.PRODUCT_ID                                       
              ,Z.PRODUCT_CATEGORY
              ,MIN(CASE WHEN Z.RN = 01 THEN Z.SUB_PRODUCT END)
               SUB_PRODUCT01                                   
              ,MIN(CASE WHEN Z.RN = 02 THEN Z.SUB_PRODUCT END)
               SUB_PRODUCT02                                   
              ,MIN(CASE WHEN Z.RN = 03 THEN Z.SUB_PRODUCT END)
               SUB_PRODUCT03                                   
              ,MIN(CASE WHEN Z.RN = 04 THEN Z.SUB_PRODUCT END)
               SUB_PRODUCT04                                   
              ,MIN(CASE WHEN Z.RN = 05 THEN Z.SUB_PRODUCT END)
               SUB_PRODUCT05                                   
              ,MIN(CASE WHEN Z.RN = 06 THEN Z.SUB_PRODUCT END)
               SUB_PRODUCT06                                   
              ,MIN(CASE WHEN Z.RN = 07 THEN Z.SUB_PRODUCT END)
               SUB_PRODUCT07                                   
              ,MIN(CASE WHEN Z.RN = 08 THEN Z.SUB_PRODUCT END)
               SUB_PRODUCT08                                   
              ,MIN(CASE WHEN Z.RN = 09 THEN Z.SUB_PRODUCT END)
               SUB_PRODUCT09                                   
              ,MIN(CASE WHEN Z.RN = 10 THEN Z.SUB_PRODUCT END)
               SUB_PRODUCT10
         FROM (SELECT PRODUCT_ID                                 
                      ,PRODUCT_CATEGORY                           
                      ,SUB_PRODUCT                               
                      ,ROWNUMBER() OVER (PARTITION BY PRODUCT_ID,
                                         PRODUCT_CATEGORY) AS RN 
                  FROM Network) Z                                   
          GROUP BY Z.PRODUCT_ID                                   
                  ,Z.PRODUCT_CATEGORY) C
                         
 WHERE A.PRODUCT_ID        = B.PRODUCT_ID                         
   AND A.PRODUCT_CATEGORY  = B.PRODUCT_CATEGORY                   
   AND A.PRODUCT_ID        = C.PRODUCT_ID                         
   AND A.PRODUCT_CATEGORY  = C.PRODUCT_CATEGORY                   
     ;                   
                                             
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group