homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Combing multiple statements

 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?




 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.


 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?



 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


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

Looks about right to me. :)

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved