Jason Jason - 13 days ago 6
MySQL Question

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

Answer

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.