Can Someone Explain the Logic in These Queries? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Can Someone Explain the Logic in These Queries?

Author

Topic

sqlnoob35
Starting Member

10 Posts

Posted - 2009-07-13 : 16:09:54

Hey guys,

I am having trouble understanding SQL logic.

I want a query that will return people who have an agty_id_cd in both APERS and ASPRS. This results in two records being returned for the same SSN.

I wrote this query and it returns no one:



select * from DSNP.PR01_T_RECIP_SYS
 where AGTY_SYS_CD = 'APERS'
 and AGTY_SYS_CD = 'ASPRS'

I don't get it. Wouldn't my conditions be true for someone with both APERS and ASPRS?

This query does return the correct results:



SELECT *                                          
         FROM DSNP.PR01_T_RECIP_SYS Z,
              DSNP.PR01_T_RECIP_SYS A                   
         WHERE Z.RECIP_SSN_NBR = A.RECIP_SSN_NBR          
         AND Z.AGTY_SYS_CD  <> A.AGTY_SYS_CD

I'm not sure what that query is doing exactly. Can someone explain? Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 16:21:56

Your first query will never return any rows because a single value cant possibly equal two different things. Think of it like all your criteria being tested against each row.

you probably want to change your criteria to: WHERE AGTY_SYS_CD in ('ASPRS', 'APERS')

Your second query would be a Self JOIN. A table joined to itself on SSN. So if you had 4 rows with the same SSN but all with different AGTY_SYS_CD values then you would bet back 12 rows

execute this code and see what I mean:


declare @t table (ssn int, agty int)
insert @t
select 1,10 union all
select 1,11 union all
select 1,12 union all
select 1,13

SELECT *                                          
  FROM @t Z,
       @t A                   
  WHERE Z.ssn = A.ssn
  AND Z.agty  <> A.agty


OUTPUT:
ssn         agty        ssn         agty
----------- ----------- ----------- -----------
1           10          1           11
1           10          1           12
1           10          1           13
1           11          1           10
1           11          1           12
1           11          1           13
1           12          1           10
1           12          1           11
1           12          1           13
1           13          1           10
1           13          1           11
1           13          1           12

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 16:36:52

>>I want a query that will return people who have an agty_id_cd in both APERS and ASPRS.
Here's one way:


select p.<explicitColumnList>
from   (
       select RECIP_SSN_NBR
       from   DSNP.PR01_T_RECIP_SYS
       where  AGTY_SYS_CD in ('ASPRS', 'APERS')
       group by RECIP_SSN_NBR
       having count(distinct AGTY_SYS_CD) > 1
       ) d
inner join DSNP.PR01_T_RECIP_SYS p
       on p.RECIP_SSN_NBR = d.RECIP_SSN_NBR
       and p.AGTY_SYS_CD in ('ASPRS', 'APERS')

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources