check if a column contains ALL the values of another column - Mysql

Question

Let's suppose I have a table T1 with people IDs and other stuff IDs, as the following

Table: T1
personID | stuffID 
    1    |    1
    1    |    2
    1    |    3
    1    |    4
    2    |    1
    2    |    4
    3    |    1
    3    |    2

And another table T2 with just one column of stuffIDs

Table: T2
stuffID
   1  
   2  
   3  

The result that I would get, by a SELECT, is a table of peopleIDs who are connected with ALL the stuffIDs of T2.

Following the example the result would be only the id 1 (the personID 3 has no to appear even if all the stuffIDs it is associated are included in T2.stuffID).

Darondarooge

Does this answer your question? Select values that meet different conditions on different rows? – Kaylenekayley

Answer 1

If I understand correctly, you want to retrieve all the personID's from T1 that have all associated stuffID's found in T2.

You can break this up as follows: First of all, find all the T1 entries that match with a nested query

SELECT personID 
FROM T1 WHERE stuffID IN (SELECT stuffID FROM t2)

Now you need to check which of the entries in this set contains ALL the stuffID's you want

GROUP BY personID
HAVING COUNT(DISTINCT stuffID) = (SELECT COUNT(stuffID) FROM t2)

and put it all together:

SELECT personID 
FROM T1 WHERE stuffID IN (SELECT stuffID FROM t2)
GROUP BY personID
HAVING COUNT(DISTINCT stuffID) = (SELECT COUNT(stuffID) FROM t2)

HTH.

Kyle

Hi Eddy! Thank you for your answer! I tried it and it should work very well (I will check it deeply in a few hours with my data). There is just another little problem: in my case T2 is a temporary table I'm creating just before the 'SELECT'. Your code works with a normal table but not if it is temporary. The Sql message is #1137 - Can't reopen table: 'T2'. Do you know why? Thank you very much – Darondarooge

Hi Marco, unless you provide the SQL you're using then I can only guess at the problem - note that you cannot refer to a temporary table more than once in the same query - see dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html – Kyle

Thank you very much. I solved creating two instances of the temporary table. Thank you again for your kind support. (by the way I'm using MySQL with phpMyAdmin 4.1.7 of the admin area of a website) – Darondarooge

Hello,correct me if i am wrong i am completely new to this. This piece of code: GROUP BY personID HAVING COUNT(DISTINCT stuffID) meassures how "much" of stuffID each personID has. So if a person has 1,6,7 as id the result is 3. Plus they do have one of their values in t2(1 is found in t2). So they will be at the final reuslt althought 1,6,7 does not match 1,2,3. Can you tell me where i am wrong as i have the same question as the one asked and this code does not work for me. – Insistency

Wow, this is an old thread. I'm afraid your understanding of how the clause is working is incorrect. The HAVING clause matches each of the stuffIDs in t2 separately, not merging them into a single value. I've made an example for you here, which can play with to help your understanding: sqltest.net/#912976 In this example, I think you would probably be expecting person 1 and person 3 to be retrieved, whereas the code actually retrieves person 1 and person 2 (as desired). Here is a nice visual explanation: w3resource.com/sql/aggregate-functions/count-having.php – Kyle

Answer 2

select personID
from T1
where stuffID in (select stuffID from t2)
group by personID
having count(distinct stuffID) = (select count(*) from t2)

I.e pick a person's stuffids which are in T2, count them (distinct only), and verify same number as in t2.

Guarantor

Answer 3

select personID from T1 group by personID having count(distinct stuffID) in (select count(distinct stuffID) from T2)

select count(distinct stuffID) from T2 <-- Would give the total number of distinct countIDs

group by personID having count(distinct stuffID) <-- after grouping by personId , counting the number of stuffIds that personId has.

So both the counts should be equal to get the desired result.

Mellette

Answer 4

Using count is an effective approach. Also, from the prospective of set theory, you can think about it in this way: for a valid personID, we shouldn't be able to find a stuffID in T2 but is not connect to this personID in T1. Therefore we can do it like this:

SELECT DISTINCT T1.personID
FROM T1
WHERE NOT EXISTS (
  SELECT T2.stuffID
  FROM T2
  WHERE T2.stuffID NOT IN (
    SELECT DISTINCT T1copy.stuffID
    FROM T1 T1copy
    WHERE T1copy.personID=T1.personID));

Raeannraeburn

Answer 5

Try this

    select personID from T1
    group by personID
    having count(distinct stuffID) >= (select count(*) from t2)

Scibert