CODEWITHSUNDEEP
sqlsql-server
Joe
Joe

Reputation: 3

SQL, Show all entries where count from different tables is unqeual

I have the following two SELECTs:

Declare @S_PWO varchar(300)
Declare @S_O varchar(300)
@S_PWO = 
(
SELECT COUNT(PWO.Attr1)
FROM Person P
JOIN PersonInOrg PIO
  ON P.UID_Person = PIO.UID_Person
JOIN Org O
  ON O.UID_Org = PIO.UID_Org
JOIN PersonWantsOrg PWO
  ON PIO.Attr2 = PWO.Attr3
WHERE 
  O.Attr4 like '%STRING%' AND
  P.UID_Person = 'XXXXXXXXX'
)
@S_O =
(
SELECT Count(O.CCC_DisplayName)
FROM Person P
JOIN PersonInOrg PIO
  ON P.UID_Person = PIO.UID_Person
JOIN Org O
  ON O.UID_Org = PIO.UID_Org
JOIN PersonWantsOrg PWO
  ON PIO.Attr2 = PWO.Attr3
WHERE 
  O.Attr4 like '%STRING%' AND
  P.UID_Person = 'XXXXXXXXX'
)

For the person XXXXXXXXX they may result in an equal count, or in an unequal count. I now need to find all persons where the count of those two Selects is unqueal. How do i do that?

Upvotes: 0

Views: 50

Answers (1)

dnoeth
dnoeth

Reputation: 60482

Your using the same Join/Where in both Selects, only the Count differs.

Simply combine both into one, Group By UID_Person and apply Having: 

SELECT P.UID_Person, Count(PWO.Attr1) AS cntAttr1, Count(O.CCC_DisplayName) AS cntDisplayName
FROM Person P
JOIN PersonInOrg PIO
JOIN PersonInOrg PIO
  ON P.UID_Person = PIO.UID_Person
JOIN Org O
  ON O.UID_Org = PIO.UID_Org
JOIN PersonWantsOrg PWO
  ON PIO.Attr2 = PWO.Attr3
WHERE 
  O.Attr4 LIKE '%STRING%'
GROUP BY P.UID_Person
HAVING Count(PWO.Attr1) <> Count(O.CCC_DisplayName)

Upvotes: 1

Related Questions