mysql select from multiple table

Question

I have 3 tables with values like below

**tbl_product**
recID      pID       price      colour
1         BDPLA-0001   1.23        White
2         BDPLA-0002   2.23        Black
3         BDPLA-0003   2.28        Blue

tbl_product_size
recID        pID       size       stock       
1            1         2.0cm       10 
2            1         3.0cm       20
3            2         2.5cm       30
4            3         3.6cm       40
5            3         3.8cm       50

tbl_order_details
recID       pID        quantity   size
201         BDPLA-0001   5        2.0cm 
202         BDPLA-0002   10       2.5cm

tbl_product = t
tbl_product_size = s
tbl_order_details = d

t.recID = s.pID
t.pID = d.pID

how can i combine the tables and produce result like this

t.pID       s.size       s.stock     d.quantity  t.price
BDPLA-0001  2.0cm        10          5           1.23
BDPLA-0001  3.0cm        20          null        1.23
BDPLA-0002  2.5cm        30          10          2.23
BDPLA-0003  3.6cm        40          null        2.28
BDPLA-0003  3.8cm        50          null        2.28

codymanix · Accepted Answer · 2010-03-31 08:39:48Z

2

You can use an union for that.

select a,b,c from table A
union
select a,b,c from table B;

The number and type of columns in each select should be the same.

answered Mar 31, 2010 at 8:39

codymanix

29.7k21 gold badges97 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Loon Yew Over a year ago

tried union wont work....need some join or etc. just that can't figure out the logic yet.

Salil · Accepted Answer · 2010-03-31 11:10:24Z

0

Your Question seems incomplete but you can try following or this will help you to undesrstand how multiple table are used in query.

select t.pID, s.size s.stock d.quantity t.price 
from  tbl_product t,  tbl_product_size s, tbl_order_details d 
where  t.recID=s.pID  and d.pID=t.pID

edited Mar 31, 2010 at 11:10

answered Mar 31, 2010 at 8:51

Salil

47.7k22 gold badges127 silver badges161 bronze badges

1 Comment

Loon Yew Over a year ago

this will product too many unrelated result. basically i want tbl_order_details data too be merge with tbl_product_size. however i will need to go through tbl_product as tbl_product can link the both tables.

GDP · Accepted Answer · 2012-08-02 17:31:22Z

0

This will do the trick

select t.pID, t.price, s.size, s.stock, d.quantity
from tbl_product t inner join tbl_product_size s on t.recID = s.pID
left outer join tbl_order_details d on t.pID = d.productCode and s.size = d.size;

edited Aug 2, 2012 at 17:31

GDP

8,1636 gold badges48 silver badges84 bronze badges

answered Apr 21, 2010 at 10:49

Loon Yew

511 silver badge7 bronze badges

Collectives™ on Stack Overflow

mysql select from multiple table

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related