CODEWITHSUNDEEP
mysqlsqlselectjoin
echo_13
echo_13

Reputation: 23

Select one column as multiple column on other table

I'm goin to start use mysql 5.1 community version.

First I have...

master_product_table

ID  Product_Code    Product_Name           Product_Details 
=====================================================================
1   000001         Raw Material 1        000001- Raw Material 1
2   000002         Raw Material 2        000002- Raw Material 2
3   000003         Raw Material 3        000003- Raw Material 3
4   000004         Raw Material 4        000004- Raw Material 4

where ID field is the PK

and master_document

Doc_no   Doc_date   Doc_type     Item_code1    Item_qty1     Item_price1     Item_code2     Item_qty2     Item_price2
=========================================================================================================================
000001   01-01-2013 BC. 2.3          1            200          $ 150             3             500           $800
000002   02-01-2013 BC. 2.7          2           1500          $ 800             4            6000          $2500
000003   03-01-2013 BC. 3.0          3           5000          $1500             1           12000          $8500
000004   04-01-2013 BC. 4.0          4          12000          $5000             2             750          $3000

where Doc_no field is the PK

What I want to get is like this...

Doc_no   Doc_date   Doc_type    Item_details1                Item_qty1    Item_price1       Item_details2                Item_qty2     Item_price2
========================================================================================================================================================
000001   01-01-2013 BC. 2.3    000001- Raw Material 1            200          $ 150       000003- Raw Material 3             500           $800
000002   02-01-2013 BC. 2.7    000002- Raw Material 2           1500          $ 800       000004- Raw Material 4            6000          $2500
000003   03-01-2013 BC. 3.0    000003- Raw Material 3           5000          $1500       000001- Raw Material 1           12000          $8500
000004   04-01-2013 BC. 4.0    000004- Raw Material 4          12000          $5000       000002- Raw Material 2             750          $3000

Any help on how to do this would be helpful thank you.

Upvotes: 2

Views: 177

Answers (3)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Tried for first time SQL FIDDLE

Took approximately 30 mins

Try the Following SQL Fiddle

SELECT md.Doc_no, md.Doc_date, md.Doc_type, 
       pd1.Product_Details Item_details1, md.Item_qty1, md.Item_price1, 
       pd2.Product_Details Item_details2, md.Item_qty2, md.Item_price2 
FROM master_document md 
INNER JOIN master_product_table pd1 ON md.Item_code1 = pd1.ID 
INNER JOIN master_product_table pd2 ON md.Item_code2 = pd2.ID
order by md.Doc_no

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT md.Doc_no, md.Doc_date, md.Doc_type, 
       mp1.Product_Details Item_details1, md.Item_qty1, md.Item_price1, 
       mp2.Product_Details Item_details2, md.Item_qty2, md.Item_price2 
FROM master_document md 
INNER JOIN master_product_table mp1 ON md.Item_code1 = mp1.ID 
INNER JOIN master_product_table mp2 ON md.Item_code2 = mp2.ID ;

Upvotes: 1

N3sh
N3sh

Reputation: 878

this is a quite basic question concerning SQL. Giving you a direct solution now will surely not help you in the future.

Try to get a smaller example and start learning how to use 'SELECT x FROM y WHERE x = w', as well as 'SELECT x as z FROM y' (this renames the column x into z)

And then, if you get any more questions, add them here.

Have fun ;)

Upvotes: 0

Related Questions