simple Group By

Question

Sorry for this simple question, but i'm missing the forest through the trees.

These are my tables (i left the sparepart-table because i'm only looking for two special fk's):

I need the distinct rows from tabData which are referenced in the child-table tabDataDetail with fiSparePart=8837 and 8969. The following gives me only that rows which have at least one sparepart, but i need those which have both. They are also not distinct, because of the having clause.

SELECT     tabData.idData
FROM         tabData INNER JOIN
                      tabDataDetail ON tabData.idData = tabDataDetail.fiData
GROUP BY tabData.idData, tabDataDetail.fiSparePart
HAVING   tabDataDetail.fiSparePart IN (8837, 8969)
ORDER BY tabData.idData

Thanks for your time.

Answer 1

Try this

SELECT     tabData.idData
FROM         tabData INNER JOIN
                      tabDataDetail ON tabData.idData = tabDataDetail.fiData
WHERE tabDataDetail.fiSparePart IN (8837, 8969)
GROUP BY tabData.idData, tabDataDetail.fiSparePart
HAVING COUNT(distinct tabDataDetail.fiSparePart  ) > 1
ORDER BY tabData.idData

Here is an example

create table #bla (idData int, fiSparePart int)
insert #bla values(1,8837)
insert #bla values(1,8969)
insert #bla values(2,8837)
insert #bla values(2,8837)
insert #bla values(2,8837)



SELECT     idData
FROM        #bla
WHERE   fiSparePart IN (8837, 8969)
GROUP BY idData
HAVING COUNT(distinct fiSparePart) > 1

Answer 2

 SELECT     tabData.idData
 FROM         tabData INNER JOIN
                  tabDataDetail ON tabData.idData = tabDataDetail.fiData
 WHERE   tabDataDetail.fiSparePart IN (8837, 8969)
 GROUP BY tabData.idData, tabDataDetail.fiSparePart
 ORDER BY tabData.idData

Answer 3

This should give you the tabData records that have both 8837 and 8969 as spare parts. You can use DISTINCT, and you don't need the group by.

SELECT  
 DISTINCT tabData.idData
 FROM    tabData 
 WHERE   EXISTS (SELECT *
                  FROM tabDataDetail
                 WHERE tabData.idData = tabDataDetail.fiData
                   AND tabDataDetail.fiSparePart = 8837)
   AND   EXISTS (SELECT *
                  FROM tabDataDetail
                 WHERE tabData.idData = tabDataDetail.fiData
                   AND tabDataDetail.fiSparePart = 8969)
 ORDER BY tabData.idData

Answer 4

You don't need two tables for this, if all you're getting from tabData is the primary key (which also exists in tabDataDetail as the foreign key). This is assuming tabData is a parent table to tabDataDetail.

SELECT a.fiData
FROM (SELECT fiData FROM tabDataDetail WHERE fiSparePart = 8837) a
   INNER JOIN
     (SELECT fiData FROM tabDataDetail WHERE fiSparePart = 8969) b
   ON a.fiData = b.fiData