...

View Full Version : query for item on 2 paramaters



turpentyne
12-01-2011, 04:49 AM
I am stumped on how to do this query. I want to search a table and get the items that appear in the database with matching numbers in the variable column. So if I search for any fruit in this table that have been entered in this table for both 1 and 23, I should get Apple.



item_id | variable
------------+----------------
grape | 1
------------+----------------
peach | 23
------------+----------------
orange | 22
------------+----------------
Apple | 1
------------+----------------
Apple | 23
------------+----------------

This query below gives me no results

SELECT item_id FROM `connect_tables` JOIN items ON connect_tables.item_id = items.item_id
WHERE connect_tables.term_id LIKE 1 AND connect_tables.item_id LIKE 23

This query gives me too many entries: apples...grapes and peaches. I only want apples.

SELECT item_id FROM `connect_tables` JOIN items ON connect_tables.item_id = items.item_id
WHERE connect_tables.term_id LIKE 1 OR connect_tables.item_id LIKE 23

I hope that made sense.

Old Pedant
12-01-2011, 05:29 AM
Well, you didn't bother to show us what is in your connect_table so I will have to ignore that until you do.

But what you want is not hard:



SELECT item_id, COUNT(*) AS howmany
FROM items
WHERE variable IN ( 1, 23 )
GROUP BY item_id
HAVING howmany = 2

Old Pedant
12-01-2011, 05:29 AM
And NEVER use LIKE unless you use a wild card character on the right side of the LIKE. It just slows things down and is pointless.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum