PHP MySQL Group BY Having issue

Question

Suppose I have a table like:

ID|Word |Reference
1 |Dog  |1
1 |Fish |2
1 |Sheep|3
2 |Dog  |4
2 |Fish |5
3 |Sheep|6
4 |Dog  |7

I want to select all ID's that have the word Dog AND Sheep. So the result should be ID's: 1 and 2. I tried using this query:

SELECT ID FROM `Table` WHERE Word='Dog' OR Word='Fish' GROUP BY ID Having Word='Dog AND Word='Fish'

However, this AND in the Having clause makes me get 0 results. So, am I doing something wrong or is there another way to achieve wat I want based on MySQL query only (to optimize speed, since it has to search through many rows with the same setup as in the example above)

Basically the problem is the AND statement over multiple rows with the same ID.

UPDATE:
I need to get the reference for the ID's that where found. E.g. when the ID 1 and 2 are returned I need to know that ID 1 has reference 1 and 2. ID 2 has reference 3 and 4. Currently, I'm using this query:

SELECT ID FROM `Test` WHERE Word in ('Dog', 'Fish') GROUP BY ID HAVING  count(DISTINCT Word) = 2;

Thanks

Answer 1

The answer for my problem is solved using underneath query:

SELECT ID, GROUP_CONCAT(Reference) as ReferencesGrouped FROM `Test` WHERE Word in ('Dog', 'Fish') GROUP BY ID HAVING  count(DISTINCT Word) = 2;

This will return me:

ID|ReferencesGrouped
1 |1,4
2 |4,5

Answer 2

Here are two solutions that return the correct records, the first as individual records by ID and Reference, and the second with one record per ID and the Words and References as comma separated in columns.

Setup table and populate rows:

DROP TABLE IF EXISTS `list1`;

CREATE table `list1` (
    id int(10),
    Word varchar(10),
    Reference int(10) 
);

INSERT INTO `list1` (`ID`, `Word`, `Reference`) 
VALUES 
(1, 'Dog',1),
(1 ,'Fish',2),
(1 ,'Sheep',3),
(2 ,'Dog',4),
(2 ,'Sheep',5),
(3 ,'Sheep',6),
(4 ,'Dog',7);

Returns one row for each combination of ID and Word

SELECT 
    t.`ID`,
    t.`Word`,
    t.`Reference`
FROM `list1` as t
JOIN (
    SELECT 
        t1.`ID` as `ref_id`
    FROM `list1` AS t1
    WHERE `Word` in ('Sheep','Dog')
    GROUP BY t1.`ID`
    HAVING count(DISTINCT t1.`Word`) = 2
) AS ts
ON t.`ID` = ts.`ref_id`
WHERE t.`Word` in ('Sheep','Dog')
ORDER BY t.`ID`,t.`Word`;

Results

ID  |   Word    |   Reference   
1   |   Dog     |   1
1   |   Sheep   |   3
2   |   Dog     |   4
2   |   Sheep   |   5

Returns one row per ID, with a comma separated list of Words in one column, and a comma separated list of Reference in another.

SELECT 
    t.`ID`,
    GROUP_CONCAT(t.`Word`) AS `Words`,
    GROUP_CONCAT(t.`Reference`) AS `References`
FROM `list1` as t
JOIN (
    SELECT 
        t1.`ID` as `ref_id`
    FROM `list1` AS t1
    WHERE `Word` in ('Sheep','Dog')
    GROUP BY t1.`ID`
    HAVING count(DISTINCT t1.`Word`) = 2
) AS ts
ON t.`ID` = ts.`ref_id`
WHERE t.`Word` in ('Sheep','Dog')
GROUP BY t.`ID`
ORDER BY t.`ID`,t.`Word`;

Results:

ID  |   Words       |   References  
1   |   Dog,Sheep   |   1,3
2   |   Dog,Sheep   |   4,5

Answer 3

Here's one way to do it by joining your table to itself.

SELECT t1.id FROM `Table` t1
INNER JOIN `Table` t2 ON t1.id = t2.id
WHERE t1.word='Dog' AND t2.word='Sheep';

Answer 4

You need to join the table on itself. This way you can pick up where the id's are the same for instances where dog and sheep overlap. Try this:

declare @t table (id int ,  Word varchar(10) )
insert into @t (ID, Word) values (1, 'Dog'),
(1 ,'Fish'),
(1 ,'Sheep'),
(2 ,'Dog'),
(2 ,'Sheep'),
(3 ,'Sheep'),
(4 ,'Dog')

select t.ID
from @t as t
join @t as t1 on t1.id = t.id
where t.word = 'Dog' and t1.word = 'Sheep'