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!
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!
it’s the GROUP BY clause which determines that the id is the same for all returned results
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?
WHERE cat_id = 2 AND myvalue = 'tom'
OR cat_id = 3 AND myvalue = 'jill'
OR cat_id = 6 AND myvalue = 'sally'
HAVING COUNT(*) = 3
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?
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