CODEWITHSUNDEEP
sql-server
Nandhi
Nandhi

Reputation: 495

WHERE condition based in the count row in another query

I am trying to make a query using a condition based in the amont of rows obtained in another query. But I cannot get the correct way. Does any body can help me ? What I am trying to do is :

SELECT    ChildDummyTable.Id ChildDummyTableId,    
ParentDummyTable.Id ParentDummyTableId
FROM    [DummyTable]   ChildDummyTable
 INNER JOIN [DummyTable] ParentDummyTable 
     ON ParentDummyTable.Id = ChildDummyTable.ParentDummyRowId 
     where (SELECT DummyRow2 FROM DummyTable2 WHERE DummyTableId=ChildDummyTable.Id
        except SELECT DummyRow2 FROM DummyTable2 ParentDummyTable2
         WHERE ParentDummyTable2.DummyTableId=DummyTable.Id).COUNT(DummyRow2)>0

Upvotes: 1

Views: 42

Answers (2)

orgtigger
orgtigger

Reputation: 4132

with a as
(SELECT ChildDummyTable.Id ChildDummyTableId
    ,ParentDummyTable.Id ParentDummyTableId
    ,ROW_NUMBER() OVER(PARTITION by 
                ChildDummyTable.Id ChildDummyTableId
        ,ParentDummyTable.Id ParentDummyTableId 
) 
aS duplicateRecCount

FROM    [DummyTable]   ChildDummyTable
  INNER JOIN [DummyTable] ParentDummyTable 
     ON ParentDummyTable.Id = ChildDummyTable.ParentDummyRowId 
)

Select * from a 
where duplicateRecCount > 1

Upvotes: 1

Lamak
Lamak

Reputation: 70668

You can use EXISTS:

SELECT  ChildDummyTable.Id ChildDummyTableId,    
        ParentDummyTable.Id ParentDummyTableId
FROM [DummyTable] ChildDummyTable
INNER JOIN [DummyTable] ParentDummyTable 
    ON ParentDummyTable.Id = ChildDummyTable.ParentDummyRowId 
WHERE EXISTS(SELECT DummyRow2 FROM DummyTable2 
             WHERE DummyTableId=ChildDummyTable.Id
             EXCEPT 
             SELECT DummyRow2 FROM DummyTable2 ParentDummyTable2
             WHERE ParentDummyTable2.DummyTableId=DummyTable.Id)

Upvotes: 2

Related Questions