Stuck in a group by

Question

table 1 - 
pid cid
901 101
901 102
901 103
902 102
902 105
903 105

table 2 -
cid
101
102
105

I need the Pid from table1 to show ONLY if ALL the CID's match the CID from Table 2. That means in the case of 901 which has CID 101,102 and 103 should NOT show in select statement cause 103 does not exist in table 2.

Answer 1

Try the following, maybe this will get you in the right direction.

This will get you all PIDs in table 1, which have all of its' CIDs located in table 2

From what gathered from the question, the PIDs in table 1 do not necessarily have to have all CIDs that are in table 2, it just has to have CIDs that are located in table 2, and if one of its' CIDs are not in table 2 then the PID becomes invalid.

DECLARE @table1 TABLE   (
                        pid INT,
                        cid INT
                        )

DECLARE @table2 TABLE   (
                        cid INT
                        )

INSERT  @table1
SELECT  901, 101 UNION ALL
SELECT  901, 102 UNION ALL
SELECT  901, 103 UNION ALL
SELECT  902, 102 UNION ALL
SELECT  902, 105 UNION ALL
SELECT  903, 105

INSERT  @table2
SELECT  101 UNION ALL
SELECT  102 UNION ALL
SELECT  105

DECLARE @temp TABLE (
                    pid     INT,
                    cid     INT,
                    test    INT
                    )

INSERT      @temp
SELECT      a.pid,
            a.cid,
            b.pid
FROM        @table1 a
LEFT JOIN   (
            SELECT      t1.pid,
                        t1.cid
            FROM        @table1 t1
            LEFT JOIN   @table2 t2
                    ON  t1.cid = t2.cid
            WHERE       t2.cid IS NULL
            ) b
        ON  a.pid = b.pid
        AND a.cid = b.cid

-- Compare the counts of nulls
SELECT      a.pid
FROM        (
            SELECT      pid,
                        COUNT(1) AS cnt
            FROM        @temp
            GROUP BY    pid
            ) a
INNER JOIN  (
            SELECT      pid,
                        COUNT(1) AS cnt
            FROM        @temp
            WHERE       test IS NULL
            GROUP BY    pid
            ) b
        ON  a.pid = b.pid
        AND a.cnt = b.cnt

Answer 2

try this:

DECLARE @table1 table (pid int, cid int)
DECLARE @table2 table (cid int)

INSERT INTO @table1 VALUES (901,101)
INSERT INTO @table1 VALUES (901,102)
INSERT INTO @table1 VALUES (901,103)
INSERT INTO @table1 VALUES (902,102)
INSERT INTO @table1 VALUES (902,105)
INSERT INTO @table1 VALUES (903,105)

INSERT INTO @Table2 VALUES (101)
INSERT INTO @Table2 VALUES (102)
INSERT INTO @Table2 VALUES (105)

SELECT
    dt.pid
    FROM (SELECT
              t1.pid, t2.cid
              FROM @table1                 t1
                  LEFT OUTER JOIN @table2  t2 ON t1.cid=t2.cid
         ) dt
    GROUP BY dt.pid
    HAVING COUNT(dt.pid)=COUNT(dt.cid)

Answer 3

I'm interpreting your question to mean you want a list of pids from Table1 in which every cid from Table2 is matched. In other words, in the following example, 901 is matched, but 902, 903 and 904 are not:

 Table1             Table2
pid | cid          cid
---------          ----
901 | 100          100
901 | 101          101
902 | 101
903 | 100
903 | 105
904 | 100
904 | 101
904 | 105

Here's the query:

DECLARE @NUM_CIDS INT
SELECT @NUM_CIDS = COUNT(1) FROM Table2

SELECT Table1.pid
FROM Table1
INNER JOIN Table2
ON Table1.cid = Table2.cid
GROUP BY Table1.pid
HAVING COUNT(Table2.cid) = @NUM_CIDS

It works by joining the two tables, then only returning the pids which have the right number of cids. It's guaranteed to work, since if a record in Table1 doesn't match, it won't participate in the join. And if the pid isn't complete, it will be rejected by the HAVING clause.

Answer 4

How about

SELECT DISTINCT PID --Get all the other PIDS
FROM Table1
WHERE PID NOT IN
(
    SELECT PID --Get the PIDS From Table 1 That Have a CID NOT IN Table2
    FROM Table1 
    WHERE CID NOT IN (
        SELECT CID    --Get All The Table 2 CIDs
        FROM Table2 
    )
)

Answer 5

I do believe you are looking for something like this:

Select distinct pid
from table1 t1
  join table2 on t1.cid = table2.cid
group by pid
having count(t1.cid) = (select count(cid) from table1 where pid = t1.pid)

Answer 6

No need to use a nested query, quicker to do a natural join. Not sure what type of database you're using, but it should still work. Only need the distinct if you want to only return each correct pid once.

SELECT DISTINCT t1.pid
FROM   table1 t1, table2 t2
WHERE  t1.cid = t2.cid

Answer 7

So, you want PIDs from table1 where there is no pair (PID, CID) in table1 such that PID is not in table2. ?

select pid from (select distinct pid from table1) table1_pids
where not exists (select 1 from table1 where table1.pid = table1_pids.pid
            and not exists (select 1 from table2 where table2.cid = table1.cid));

Another way:

select pid from table1 left join table2 on table1.cid = table2.cid
group by pid
having sum(case when table2.cid is null then 1 else 0 end) = 0

Answer 8

If I understand you right, your goal is to only get rows where there aren't any unmatched cids - that is, if there's even one row with the same pid and a non-existent cid, don't show any of the rows with that pid. If that's the case, the most straightforward way to code it is using an exists on the same table, like so:

SELECT * 
  FROM 
    [table 1] T1A 
  WHERE 
    /* there are no rows in my same table ... */
    NOT EXISTS (
        SELECT * 
          FROM 
            [table 1] T1B 
          WHERE 
            T1A.pid = T1B.pid 
            /* ... that do not have a matching cid in table 2 */
            AND T1B.cid NOT IN (SELECT cid FROM [table 2])
    )

This returns the 3 rows with pid=902, and none of the ones with pid=901.

Answer 9

I assume you need only a PID as well as that CID is not negative. Then

Solution: (version without COUNT(..) and EXIST(..))

SELECT      table1.pid
FROM        table1
LEFT JOIN   table2 ON table1.cid = table2.cid
GROUP BY    table1.pid
HAVING      MIN(COALESCE(table2.cid, -1)) <> -1

returns:

pid
-----------
902
903

Answer 10

I don't specifically use sql/tsql but I think a solution in mysql is the following, perhaps someone can translate it into sql/tsql as I think it is a simple solution to the problem.

SELECT
    count(t2.cid IS NULL) AS noMatch
        ,pid
    FROM table1       t1
    LEFT JOIN table2  t2 ON t2.cid = t1.cid
    HAVING noMatch = 0;