Welcome to WebmasterWorld Guest from 54.145.144.101

Forum Moderators: open

Message Too Old, No Replies

Combing multiple statements

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

5+ Year Member



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
11:38 pm on Dec 29, 2012 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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.
12:34 am on Dec 30, 2012 (gmt 0)

5+ Year Member



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
1:57 am on Dec 30, 2012 (gmt 0)

5+ Year Member



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

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

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



Looks about right to me. :)
 

Featured Threads

Hot Threads This Week

Hot Threads This Month