Select Multiple Values From Single Column

Question

I would like to select multiple values from a single column in a database table that equal to a number of values. I want all these values to match otherwise it should return no rows. I do not want to use "IN" as that is equal to "OR".

The following is a basic mockup of what it should do but it needs to be dynamic as I wish to use it with a PDO statement. If the database only contains id's 1 and 2 it should fail ie return no rows.

SELECT
id
FROM
reports
WHERE
id=1 AND id=2 AND id=3

I have the current code as follow which is incorrectly returning zero rows:

SELECT id,title
FROM reports
WHERE id IN (1,2)
GROUP BY title 
HAVING COUNT(DISTINCT id) = 2

My current table structure is as follows: http://www.sqlfiddle.com/#!2/ce4aa/1

Mahmoud Gamal · Accepted Answer · 2013-01-20 10:08:22Z

5

You have to use HAVING COUNT(id) = 3 to ensure that the selected rows have all the three id's. Something like:

SELECT *
FROM reports
WHERE id = 1 OR id = 2 OR id = 3 -- Or id IN(1, 2, 3)
GROUP BY SomeOtherField
HAVING COUNT(DISTINCT id) = 3;

Or:

SELECT *
FROM reports
WHERE SomeOtherField IN (SELECT SomeOtherField 
                         FROM reports
                         WHERE id = 1 or id = 2 -- Or id IN(1, 2, 3)
                         GROUP BY SomeOtherField
                         HAVING COUNT(DISTINCT id) = 3
                        );

Note that: You have to GROUP BY SomeOtherField where SomeOtherField is other field than id because if you GROUP BY id with HAVING COUNT(id) you won't get any records, since COUNT(id) will be always = 1.

Edit: fixed WHERE clause, OR's instead of AND's.

SQL Fiddle Demo

edited Jan 20, 2013 at 10:08

answered Jan 20, 2013 at 9:25

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

Mahmoud Gamal Over a year ago

@Nuvolari Can you please show me the table structure and some sample data? note that you have to GROUP BY other field than that id becuase if you GROUP BY id this won't give you nothing since COUNT(id) will be 1 always since you are grouping by it.

Mahmoud Gamal Over a year ago

@Nuvolari Sorry, it should be WHERE id = 1 OR id = 2 OR id = 3 or WHERE id IN(1, 2, 3) instead of WHERE id = 1 AND id = 2 AND id = 3 like my updated answer.

Mahmoud Gamal Over a year ago

@Nuvolari is the title is unique for each id? Can you please post the table structure? it would help, thanks.

Mahmoud Gamal Over a year ago

@Nuvolari But, there are no reports titles that had all the report_id 1, 2, 3 in that sample data. This assuming that you are looking for those titles that had all the report_id's 1, 2, 3. Is this really what you are looking for? Also what the right output from that table that you are looking for? Thanks.

Nuvolari Over a year ago

@ Mahmoud Gamal I updated the table with more data: sqlfiddle.com/#!2/ce4aa/1 I am just looking to see whether the query returns rows or no rows and do something based upon that.

|

SarangRN · Accepted Answer · 2023-04-10 14:06:06Z

0

you can also try following.

SELECT *
FROM reports
GROUP BY SomeOtherField
HAVING COUNT(DISTINCT id) = 3
and id IN(1, 2, 3)

answered Apr 10, 2023 at 14:06

SarangRN

1502 silver badges17 bronze badges

Collectives™ on Stack Overflow

Select Multiple Values From Single Column

2 Answers 2

22 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

22 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related