Select statement with multiple joins
Posted on 2007-03-25
How can I select fields from multiple select statements into one select statement. I have the following selects with a common field called contact_id. The select statements are greatly simplified for this purpose. Current, Zero30, etc are calculated and not fields in a table.
I want to be able to generate ONE record with each of the values from the joined selects. I've included a sample but don't know how the joins should be phrased (JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN).
SELECT a.contact_id, b.Current, c.Zero30, d.ThirtyOne60, e.SixtyOne90, f.Over90 FROM accounting a
(HOW TO JOIN EACH???)
(SELECT contact_id, Current FROM ar) AS b ON a.contact_id = b.contact_id JOIN?
(SELECT contact_id, Zero30 FROM ar) AS c ON a.contact_id = c.contact_id JOIN?
(SELECT contact_id, ThirtyOne60 FROM ar) AS d ON a.contact_id = d.contact_id JOIN?
(SELECT contact_id, SixtyOne90 FROM ar) AS e ON a.contact_id = e.contact_id JOIN?
(SELECT contact_id, Over90 FROM ar) AS f ON a.contact_id = f.contact_id
WHERE a.contact_id = 455