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

Answers


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)


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

Need Your Help

Programmatically-created subviews and viewDidUnload

iphone objective-c ios memory-management

For a view controller, any outlets that you set in Interface Builder must be released

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.