January 27, 2011 at 12:30 pm
Join these three together and get unique account from third table:
table1
userid name
1 joe
2 mike
3 scott
table2
userid account
1 10
2 20
2 30
table3
account city serial #
10 new york 11
20 miami 22
30 boston 33
20 miami 44
20 miami 55
30 boston 4444
30 boston 66
select distinct name, table2.account, table3.city
from table1
left join table2 on table1.userid = table2.userid
left join table3 on table2.account = table3.account
output
joe 10 new york
mike 20 miami
mike 30 boston
I want to try to write this getting a distinct accounts from table3, but cannot seem to see what's wrong (I try here joining back to itself):
select name, table2.account, tb3result.city
from table1
left join table2 on table1.userid = table2.userid
left join (select distinct account from table3)
as tab3 on table2.account = tab3.account
left join table3 as tb3result on table2.account = tb3result.account
joe 10 new york
mike 20 miami
mike 20 miami
mike 20 miami
mike 30 boston
mike 30 boston
mike 30 boston
scott NULLNULL
Definitely not what I want.
January 27, 2011 at 12:56 pm
What results are you expecting to get?
It looks like your first output window is giving you what you are asking for -- did that come from your first query? If not, what exactly did the first query return?
Rob Schripsema
Propack, Inc.
January 27, 2011 at 1:25 pm
I'm not sure why you're joining to your table3 twice, but that is what is causing the problem. The first join is okay, because of the distinct clause, but the second join doesn't use the distinct clause, so you're getting a row for each occurrence of the city in the original table, hence your duplicates. Try the following instead:
select name, table2.account, tab3.city
from table1
left join table2 on table1.userid = table2.userid
left join (select distinct account, city from table3)
as tab3
This assumes that there is only one city per account. If you have multiple cities, then you have a problem. You can use either Min() and/or Max() or use FOR XML PATH with a subquery to get a unique result for each account.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 27, 2011 at 2:06 pm
Rob Schripsema (1/27/2011)
What results are you expecting to get?It looks like your first output window is giving you what you are asking for -- did that come from your first query? If not, what exactly did the first query return?
Yes, I'm aiming for the first output, which appears to be correct.
January 27, 2011 at 2:08 pm
I'm not sure why you're joining to
This assumes that there is only one city per account. If you have multiple cities, then you have a problem. You can use either Min() and/or Max() or use FOR XML PATH with a subquery to get a unique result for each account.
Drew
Yes, I realize that, but that's what the specifications say. They're focusing more on unique accounts.
On your query
I get Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'tab3'.
January 27, 2011 at 2:21 pm
I forgot to include the ON statement.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 28, 2011 at 7:40 am
I got better information...They want just the first account number.
select name, table2.account, table3.city
from table1
left join table2 on table1.userid = table2.userid
left join (select top 1 account from table3)
as tab3 on table2.account = tab3.account
But this gives me a
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "table3.city" could not be bound.
If I do
select name, table2.account, tab3.city
from table1
left join table2 on table1.userid = table2.userid
left join (select top 1 account from table3)
as tab3 on table2.account = tab3.account
Msg 207, Level 16, State 1, Line 1
Invalid column name 'city'.
January 28, 2011 at 9:51 am
I came across this http://www.reddybrek.com/post/Return-Top-Row-from-Left-Join-using-SQL-Server-2005.aspx
But I notice the database is SQL 2000 compatibility mode still.
I sort of don't want to do a top 1 columna, columnb.
Guess I'll have to write a cursor now.
January 28, 2011 at 1:20 pm
Sailor (1/28/2011)
I got better information...They want just the first account number.select name, table2.account, table3.city
from table1
left join table2 on table1.userid = table2.userid
left join (select top 1 account from table3)
as tab3 on table2.account = tab3.account
But this gives me a
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "table3.city" could not be bound.
If I do
select name, table2.account, tab3.city
from table1
left join table2 on table1.userid = table2.userid
left join (select top 1 account from table3)
as tab3 on table2.account = tab3.account
Msg 207, Level 16, State 1, Line 1
Invalid column name 'city'.
Is there a particular reason that the database is still in 2000 compatibility mode?
You're getting the error about the city, because it's not included in your subquery. In 2000 compatibility mode, I would rewrite this using a correlated subquery instead of using the left outer join.
select name, table2.account, (SELECT TOP 1 city FROM Table3 WHERE table3.account = table2.account )
from table1
left join table2
on table1.userid = table2.userid
In SQL 2005, I'd be more inclined to use a CTE with Row_Number to find the first row from table3 for each account, although you'd probably want to check both approaches against your data to see which is more efficient.
Drew
PS: None of this code is tested, because you haven't supplied easily digestible data as recommended in the forum etiquette http://www.sqlservercentral.com/articles/Best+Practices/61537/. If you want fully tested code, I suggest you follow those guidelines.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 29, 2011 at 1:01 pm
Is there a particular reason that the database is still in 2000 compatibility mode?
They haven't test for SQL 2005 yet.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply