CODEWITHSUNDEEP
mysqlselectunionconditional-statementswhere-clause
Alex K
Alex K

Reputation: 7217

MySQL select one field from table WHERE condition is in multiple rows

Tried to find the answer, but still couldn't.. The table is as follows:

id, keyword,   value
1   display    15.6
1   harddrive  320
1   ram        3

So what i need is something like this.. Select an id from this table where (keyword="display" and value="15.6") AND (keyword="harddrive" and value="320") There's also a possibility that there will be 3 or 4 such keyword conditions which should result into returning one id (one row)

It seems there's something to deal with UNION but i didn't use it before so i can't figure it out

Thanks in advance

Upvotes: 3

Views: 10754

Answers (2)

SCC
SCC

Reputation: 509

SELECT DISTINCT id FROM table
WHERE 
(keyword="display" and value=15.6) OR (keyword="harddrive" and value=320)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453067

This is a relational division problem. Something like the following should do it.

SELECT id
FROM your_table
WHERE 
(keyword="display" and value="15.6") OR (keyword="harddrive" and value="320")
GROUP BY id
HAVING COUNT(*) = 2

I'm assuming that your table has appropriate constraints such that it is impossible for there to be a completely duplicated row. (e.g. there is a PK on id, keyword)

Upvotes: 3

Related Questions