homepage Welcome to WebmasterWorld Guest from 54.211.219.68
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Combing multiple statements
ahmed24

5+ Year Member



 
Msg#: 4531887 posted 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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4531887 posted 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

5+ Year Member



 
Msg#: 4531887 posted 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

5+ Year Member



 
Msg#: 4531887 posted 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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4531887 posted 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