Not sure how to write query

Databases
1

I have a problem, that i can’t figure out, I fear I am making it more complicated than it needs to be, here it is:

I have a table


id    cat_id     myvalue      object_id
--------------------------------
1     2          Tom               1000
2     3          Jill                  1000
3     6          Sally               2000
4     2          hank               4000

I want to return only results, where the object_id is the same for all 3 criteria’s

this should return no results because no objects fulfill all these criteria




SELECT * FROM `mytable` WHERE ((cat_id = '2' AND myvalue='tom') OR (cat_id = '3' AND myvalue='jill') OR (cat_id = '6' AND myvalue='sally'))

Because I am using “OR” I am not getting the result I am looking for. But when I use “AND” I don’t get the result I am looking for either.

Let me know if I am explaining this clearly. Thanks in advance for any help!

2

Thanks i think I am starting to get it, I changed the GROUP BY id, to GROUP BY object_id and now it works exactly how i need it to.

Thanks so much for explaining the solution!

3

no :slight_smile:

it’s the GROUP BY clause which determines that the id is the same for all returned results

4

not hi-jacking the thread but, I am trying to understand the query

Is it the HAVING clause which determines that the object_id is the same for all returned results?

bazz

5 
SELECT id
  FROM mytable
 WHERE cat_id = 2 AND myvalue = 'tom'
    OR cat_id = 3 AND myvalue = 'jill'
    OR cat_id = 6 AND myvalue = 'sally'
GROUP
    BY id
HAVING COUNT(*) = 3

:slight_smile:

6

Thanks for the quick response! I have tried learning a little bit about the HAVING clause, but I am not sure I understand it all that well, Would any one be interested in giving a plain-dumbed-down explanation? :slight_smile:

7

sure

the GROUP BY clause collapses multiple rows into one

each row in each grouping has the same value for the grouping column(s), in this case id

so GROUP BY groups all rows with the same id into a group, and HAVING then applies a condition to the group

in this case, the condition is that there must be 3 rows in the group

in other words, if a particular id has all three conditions which were given in the WHERE clause, then that id satisfies the HAVING clause, and thus that id is returned by the query