Q:

Joining to table with multiple FKs and Rows

I have the following query:
SELECT p.id, 
    p.firstname,
    **p.address1id,
    p.address2id,**
    r.invoice_id, 
    i.authcode

FROM    membershiprenewals r, 
    Profile p, 
    Invoice i

WHERE   r.orgID = 1 
    and r.period_id = 3
    and r.status = 0
    and r.profile_id = p.id 
    and r.invoice_id = i.id;

This table selects a Users Profile and a few related details.
A Profiles Addresses are stored in another table (profileaddress). And a Profile can have 2 addresses.  These addresses are referenced using p.address1 and p.address2.
I need to extend this query to join on the profileaddress table to get BOTH addresses and combined into the single record.
So the results I would need would be the following columns
p.id | p.firstname | .. etc .. | profileaddress1.address | profileaddress1.town | profileaddress2.address | profileaddress2.town | .. etc

I've been playing around with JOIN statements for hours, but just can't seem to crack it.
Any help hugely Appreciated !!
Jason

A:

First, never use commas in the FROM clause.  Always use proper, explicit JOIN syntax.  So, your query should be:
SELECT p.id, p.firstname, **p.address1id, p.address2id,**
       r.invoice_id, i.authcode
FROM membershiprenewals r JOIN
     Profile p
     ON r.profile_id = p.id JOIN
     Invoice i
     ON r.invoice_id = i.id
WHERE r.orgID = 1 AND r.period_id = 3 AND r.status = 0;

Then you want two joins to the address table:
SELECT p.id, p.firstname, p.address1id, p.address2id,
       pa1.address, pa1.town,
       pa2.address, pa2.town,
       r.invoice_id, i.authcode
FROM membershiprenewals r JOIN
     Profile p
     ON r.profile_id = p.id JOIN
     Invoice i
     ON r.invoice_id = i.id LEFT JOIN
     profileaddress pa1
     ON p.address1id = pa1.id LEFT JOIN
     profileaddress pa2
     ON p.address2id = pa2.id 
WHERE r.orgID = 1 AND r.period_id = 3 AND r.status = 0;

This uses LEFT JOIN in case one of the addresses is missing.

