CODEWITHSUNDEEP
mysqlsqlmariadb
Simon
Simon

Reputation: 41

Is there a way to select values from a column based on the comparison of another column with a subquery returning multiple rows?

I'll try to clarify the question in the title with an example,

You have a table X, this table contains the columns foo VARCHAR and bar INT The values in the table are as following:

   TABLE X
-------------
| foo | bar |
-------------
| 'a' |  1  |
| 'a' |  2  |
| 'a' |  3  |
| 'b' |  1  |
| 'b' |  2  |
| 'c' |  1  |
-------------

And in order to simplify, we're going to take an example of what the subquery could return, let's say (1,2)

What I would like to obtain using (1,2) is this :

----------
| result |
----------
|  'a'   |
|  'b'   |
----------

Because a and b are the only ones with 1 and 2 in the bar column.

So basically, what I want to do is get all the foo values that have all the bar values returned by the subquery.

However, I don't know if this is possible because I need to group multiple columns with the same foo and then compare their bar to the values returned by the subquery.

If you want to try yourself here is a Minimal Reproductive Example, you can go on https://sqliteonline.com/

Make sure to use MySQL or MariaDB

CREATE TABLE X (
    foo VARCHAR(16) NOT NULL,
    bar INT NOT NULL,
    PRIMARY KEY (foo,bar)
);

INSERT INTO X(foo,bar) VALUES ('a', 1);
INSERT INTO X(foo,bar) VALUES ('a', 2);
INSERT INTO X(foo,bar) VALUES ('a', 3);
INSERT INTO X(foo,bar) VALUES ('b', 1);
INSERT INTO X(foo,bar) VALUES ('b', 2);
INSERT INTO X(foo,bar) VALUES ('c', 1);

You don't need to use a subquery, you can just assume that it's going to returned either (1), (1,2), (2,3), (1,2,3) or every other combination (the order doesn't matter).

Upvotes: 0

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can use aggregation and filtering:

select foo
from x
where bar in (1, 2)
group by foo
having count(*) = 2;  -- number of items in the list of bars

You example data doesn't have duplicates. But if it did, use count(distinct bar) = 2 instead.

EDIT:

If you have a subquery, then use:

with s as (
      <subquery here>
     )
select foo
from x join
     s
     on s.bar = x.bar
group by foo
having count(*) = (select count(*) from s);

Upvotes: 1

Related Questions