Getting matching rows from a cross ref table

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

Getting matching rows from a cross ref table

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-06-16 : 13:54:14

Hello,

I hava accountinfo and accountstationxref tables with key relationships. I want to get all the accounts from the accountinfo table for a user whose (one or more) stations matches with any other user's stations in the accountstationxref table. But it's not working. Here's what I'm trying:

SELECT DISTINCT ACCOUNT_SID, ACC_FIRSTNAME, ACC_LASTNAME, ACC_COMPANY FROM ACCOUNTINFO
WHERE ACCOUNT_SID IN (SELECT ACCOUNT_ID FROM ACCOUNTSTATIONXREF ASX WHERE ASX.ACCOUNT_ID = 113
AND ASX.STATION_ID IN (SELECT STATION_ID FROM ACCOUNTSTATIONXREF WHERE ASX.STATION_ID = STATION_ID))

Any idea?
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-16 : 14:02:38

[code]
SELECT *
FROM ACCOUNTINFO ai
INNER JOIN (SELECT t1.STATION_ID,t1.ACCOUNT_ID
FROM ACCOUNTSTATIONXREF t1
INNER JOIN (SELECT STATION_ID FROM ACCOUNTSTATIONXREF GROUP BY STATION_ID HAVING COUNT(DISTINCT ACCOUNT_ID)>1 ) t2
ON t1.STATION_ID=t2.STATION_ID)st
ON st.ACCOUNT_ID=ai.ACCOUNT_ID
WHERE ai.ACCOUNT_ID=113
[/code]

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-06-16 : 14:28:31

Even though its looking more logical, its giving the same output - which is the (only) record for account# 113.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-16 : 14:32:45

[code]SELECT *
FROM ACCOUNTINFO ai
INNER JOIN (SELECT t1.STATION_ID,t1.ACCOUNT_ID
FROM ACCOUNTSTATIONXREF t1
INNER JOIN (SELECT STATION_ID FROM ACCOUNTSTATIONXREF GROUP BY STATION_ID HAVING COUNT(DISTINCT ACCOUNT_ID)>1 AND SUM(CASE WHEN ai.ACCOUNT_ID=113 THEN 1 ELSE 0 END) >0 ) t2
ON t1.STATION_ID=t2.STATION_ID)st
ON st.ACCOUNT_ID=ai.ACCOUNT_ID
[/code]

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-06-16 : 15:17:19

This one is giving an error: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ai.ACCOUNT_SID" could not be bound.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-17 : 10:12:57

quote:
Originally posted by sqlbug

This one is giving an error: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ai.ACCOUNT_SID" could not be bound.

is there a column called ACCOUNT_SID in ACCOUNTINFO?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources