sql Question - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

sql Question

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-09-09 : 09:09:56

I have 2 tables. They are a one to many. In table one. I have clients in table 2 I have multiple AKA's. I have to join the tables to filter out certain AKA's, but I only want to bring back just one client. The SQL below brings back the clint multiple times with different AKA's

SELECT C.CLNT_ID, C.ORGANIZATION_NAME, C.CLNT_TYPE, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, C.NAME_SUFFIX, C.EIN, C.DUN,
TO_CHAR(C.DATE_STATUS_BEGIN, 'YYYY-MM-DD HH24:MI:SS') AS DATE_STATUS_BEGIN, C.CLNT_COMMENT, AKA.DER_CODE, AKA.AKA,
AKA.CLNTAKATP_ID
FROM CLIENTS C LEFT OUTER JOIN
CLIENT_AKAS AKA ON C.CLNT_ID = AKA.CLNT_ID
WHERE (AKA.AKA LIKE 'OG%') AND (AKA.CLNTAKATP_ID = 1 OR
AKA.CLNTAKATP_ID = 21 OR
AKA.CLNTAKATP_ID = 26 OR
AKA.CLNTAKATP_ID = 28 OR
AKA.CLNTAKATP_ID = 29 OR
AKA.CLNTAKATP_ID = 30 OR
AKA.CLNTAKATP_ID = 31 OR
AKA.CLNTAKATP_ID = 52) AND (AKA.DER_CODE = 9) AND (C.CLNT_ID = 52593)

Dave
Helixpoint Web Development
http://www.helixpoint.com

CSears
Starting Member

39 Posts

Posted - 2010-09-09 : 10:14:36

So what are you looking for? To just select the first AKA from the list? Can you post some sample result sets (expected versus actual results)

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-09-09 : 10:47:18

Ahhh. That got in the by mistake. I don't need anything from the AKA table

Dave
Helixpoint Web Development
http://www.helixpoint.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources