Combing multiple statements

Forum Moderators: open

Message Too Old, No Replies

Combing multiple statements

ahmed24

3:59 pm on Dec 29, 2012 (gmt 0)

I have 3 different tables: user_reg, user_contacts and contacts.

I have the following SQL statement that returns all the user_id's that I am interested in using:


SELECT user_id FROM user_reg WHERE year='2012' AND end_date IS NULL

I then need to use the returned user_id's to query the user_contacts table to find all the contact_id's where the user_id matches the results of the previous sql statement. e.g:


SELECT contact_id FROM user_contacts WHERE user_id='From Above User_ID'

Now that i have the contact_id, i need to select email, phone and mobile data from the contacts table where the id matches the previous contact_id. e.g:


SELECT email, phone, mobile FROM contacts WHERE id='From Above Contact_ID'

Without doing this within multiple while or foreach loops, is there any way to combine these three statements into one so that the results are returned as one?

Thanks

LifeinAsia

11:38 pm on Dec 29, 2012 (gmt 0)

This is where a JOIN is your friend. :)

You could join the first 2 results as follows:
SELECT ur.user_id, uc.contact_id
FROM user_reg ur INNER JOIN user_contacts uc ON ur.user_id=ud.user_id
WHERE ur.year='2012' AND ur.end_date IS NULL

You can join the 3rd result set using the same logic.

ahmed24

12:34 am on Dec 30, 2012 (gmt 0)

that's great. thanks. can you kindly show me how i can use the same logic to join the 3rd result so i can understand it better? using this logic I am guessing I can keep joining tables to get data from other tables by linking values up e.g. user_id?

also, if i'm selecting lets say user_id from one table and contact_id from another table, if these tables had duplicate named columns, will this effect the result? I am assuming it shouldnt as long as I am not calling lets say user_id from two different tables, correct?

thanks

ahmed24

1:57 am on Dec 30, 2012 (gmt 0)

I think i've figured out how to join the 3rd result using the same logic. it seems to be working, i've copied and pasted it below, can anyone kindly confirm if i've got the logic correct?



SELECT ur.user_id, uc.contact_id, c.email, c.phone, c.mobile
FROM user_reg ur 

INNER JOIN user_contacts uc ON ur.user_id=uc.user_id 
INNER JOIN contacts c ON uc.contact_id=c.id

WHERE ur.year='2012' AND ur.end_date IS NULL

LifeinAsia

1:33 am on Jan 2, 2013 (gmt 0)

Looks about right to me. :)