4 table query

[electricphp]

I have 4 tables named:

prod, prod_b, prod_c, prod_d

the tables all have have an identifying prod_id

How can I write a query that pulls all fields from each table for the record where prod_id = 1

SELECT * ...

 

[Kozusnik]

Either one of these should work. You may need to adjust the JOINs in the second example based upon your data.

SELECT * 
FROM prod t1,prod_b t2,prod_c t3,prod_d t4
WHERE t1.prod_id=t2.prod_id and t1.prod_id=t3.prod_id and t1.prod_id=t4.prod_id and t1.prod_id=1

or with joins (might not be the ones you want...

SELECT * 
FROM prod
LEFT OUTER JOIN prod_b on prod.prod_id=prod_b.prod_id
LEFT OUTER JOIN prod_c on prod.prod_id=prod_c.prod_id
LEFT OUTER JOIN prod_c on prod.prod_id=prod_c.prod_id
WHERE prod.prod_id = 1

There are several solutions. Just find the one that works best.

Mark

 

[Kozusnik]

I screwed up the second query...

SELECT *
FROM prod
LEFT OUTER JOIN prod_b on prod.prod_id=prod_b.prod_id
LEFT OUTER JOIN prod_c on prod.prod_id=prod_c.prod_id
LEFT OUTER JOIN prod_d on prod.prod_id=prod_d.prod_id
WHERE prod.prod_id = 1

This is why we debug I guess.
Mark

 

[r937]

not. enough. info. !!

SELECT *
FROM prod
WHERE prod_id = 1
UNION ALL
SELECT *
FROM prod_b
WHERE prod_id = 1
UNION ALL
SELECT *
FROM prod_c
WHERE prod_id = 1
UNION ALL
SELECT *
FROM prod_d
WHERE prod_id = 1



r937.com | rudy.ca