Joining Three Tables

Question

I'm trying to take data from three different tables and output it using as few queries and as little PHP code as possible.

Listed below are the tables I have and the columns in each (only listing relevant columns).

exp_members (A)
columns: member_id, group_id

exp_brandrelations (B)
columns: member_id, brand_id

exp_du_mktgmats (C)
columns: du_id, brand_id, date

I want to loop through the members who belong to group_id='5' (from A), determine which brands are assigned to each member (from B), and get a list of du_ids (from C) that correspond to each member, that have been INSERTed in the last 24 hours.

So far, I can get a list of members in group 5:
SELECT member_id, brand_id FROM exp_brandrelations
WHERE member_id IN (SELECT member_id FROM exp_members where group_id = 5)

And I can get a list of du_ids from the last 24 hours:
SELECT du_id FROM exp_du_mktgmats
WHERE date >= DATE_SUB(NOW(), INTERVAL 1 DAY)

But I'm not sure how best to tie it all together.

WNRosenberg · Accepted Answer · 2012-02-01 02:49:22Z

1

This should do it!

SELECT m.member_id, b.brand_id, d.du_id FROM exp_members m, exp_brandrelations b, exp_du_mktgmats d WHERE m.group_id = '5' AND m.member_id = b.member_id AND b.brand_id = d.brand_id AND d.date >= DATE_SUB(NOW(), INTERVAL 1 DAY)

edited Feb 1, 2012 at 2:49

WNRosenberg

1,9025 gold badges24 silver badges31 bronze badges

answered Feb 1, 2012 at 0:13

Sal

1,64512 silver badges9 bronze badges

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

1 Comment

WNRosenberg Over a year ago

This is what I needed... until I decided I needed to JOIN in 2 more tables :p but this did get me on the right track.

Borealid · Accepted Answer · 2012-02-01 00:35:54Z

SELECT du_id FROM exp_members m, exp_brandrelations r, exp_du_mktgmats a WHERE a.brand_id=r.brand_id AND r.member_id=m.member_id AND date >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND m.group_id='5'

Simon Germain · Accepted Answer · 2012-02-01 01:26:19Z

SELECT c.du_id FROM exp_du_mktgmats c LEFT JOIN exp_brandrelations b ON c.brand_id = b.brand_id LEFT JOIN exp_members a ON b.member_id = a.member_id WHERE a.group_id = 5 AND c.date >= DATE_SUB(NOW(), INTERVAL 1 DAY);

Collectives™ on Stack Overflow

Joining Three Tables

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related