get data from 4 tables using single query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

get data from 4 tables using single query

Author

Topic

alwaysonline
Starting Member

5 Posts

Posted - 2012-01-27 : 02:20:03

i have 4 tables

table1=userid,firstname

table2=userid,contactid

table3=contactid,emailid

table4=emailid,emailaddress

now after the query i want 2 get userid,firstname,emailaddress..how to get it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-27 : 02:50:32

1. you identify the columns you want is from which table
userid and firstname is from table1
emailaddress is from table4

so the SELECT statement should be


SELECT table1.userid, table1.firstname, table4.emailaddress

2. table1 and table4 does not have direct relationship, you will need to "link" it via other tables

- table1 & table2 : userid
- table2 & table3 : contactid
- table3 & table4 : emailid

3. so the JOIN statement should be


FROM       table1 
INNER JOIN table2 ON table1.userid    = table2.userid
INNER JOIN table3 ON table2.contactid = table3.contactid
INNER JOIN table4 ON table3.emailid   = table4.emailid

4. putting all together


SELECT     table1.userid, table1.firstname, table4.emailaddress
FROM       table1 
INNER JOIN table2 ON table1.userid    = table2.userid
INNER JOIN table3 ON table2.contactid = table3.contactid
INNER JOIN table4 ON table3.emailid   = table4.emailid

KH
[spoiler]Time is always against us[/spoiler]

alwaysonline
Starting Member

5 Posts

Posted - 2012-01-27 : 03:35:00

yes this is working...

now i have another table table5, vth columns ownerid,firstname...

after executing the query, i want userid,firstname,emailaddress of only those, where userid in table1 nd ownerid in table5 dont match

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-27 : 03:37:22

quote:
after executing the query, i want userid,firstname,emailaddress of only those, where userid in table1 nd ownerid in table5 dont match

do you mean you want the userid that is not ownerid in table5 ?

KH
[spoiler]Time is always against us[/spoiler]

alwaysonline
Starting Member

5 Posts

Posted - 2012-01-27 : 03:51:39

yes..i used the below statement,is that right? yes that userid should not match vth ownerid in table5

SELECT table1.userid, table1.firstname, table4.emailaddress
FROM table1
INNER JOIN table2 ON table1.userid = table2.userid
INNER JOIN table3 ON table2.contactid = table3.contactid
INNER JOIN table4 ON table3.emailid = table4.emailid
where table1.userid not in(select ownerid from table5 where ownerid=table1.userid)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-27 : 03:57:48

you can use NOT IN or NOT EXISTS


WHERE NOT EXISTS (SELECT * FROM table5 where table5.ownerid = table1.userid)

KH
[spoiler]Time is always against us[/spoiler]

alwaysonline
Starting Member

5 Posts

Posted - 2012-01-27 : 04:01:12

u mean where table1.userid NOT EXISTS (SELECT * FROM table5 where table5.ownerid = table1.userid)

alwaysonline
Starting Member

5 Posts

Posted - 2012-01-27 : 04:10:41

SELECT table1.userid, table1.firstname, table4.emailaddress
FROM table1
INNER JOIN table2 ON table1.userid = table2.userid
INNER JOIN table3 ON table2.contactid = table3.contactid
INNER JOIN table4 ON table3.emailid = table4.emailid
where table1.userid not in (SELECT * FROM table5 where table5.ownerid = table1.userid)

im getting error here as "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources