I have the following table:
Guy Table: +------+----------+------+ | guy | attribute| value| +------+----------+------+ | John | age | 12 | | John | dollars | 15 | | John | candies | 3 | | Bob | age | 15 | | Bob | dollars | 20 | | Bob | candies | 6 | +------+----------+------+
How can I get all the guys that match any conditions in its attributes and values?
Basically I want to be able to search for guys and their attributes using all types of conditions, such as gt, lt, ge, le, eq, ne, between, etc., and also be able to group these conditions using " ands "and" ors ".
For example, let's say I want to get guys who have more than 16 dollars or have less than 5 candies, so I would try something like this:
select guy,attribute,value from guy group by guy having (attribute = 'dollars' and value > 16) or (attribute = 'candies' and value < 5);
I would expect both John and Bob to be returned, but an empty set is returned instead (no matches).
and this is just a simple query, queries can be as complex as this
//get guys that have more than 2 candies and also have more than 16 dollars but no matter what, don't forget to get the guys which age is exactly 12 select guy,attribute,value from guy group by guy having ((attribute = 'candies' and value > 2) and (attribute = 'dollars' and value > 16)) or (attribute = 'age' and value = 12);
this last query should also return both John and Bob, but only John returns
Here is the code to create the table and add rows:
create table guy(guy varchar(255), attribute varchar(255), value int); insert into guy values('John', 'age', 12),('John', 'dollars', 15),('John', 'candies', 3),('Bob', 'age', 15),('Bob', 'dollars', 20),('Bob', 'candies', 6);
Keep in mind that later I could add more guys and more attributes, but all guys will always have the same number of attributes, for example, if I added "Mario", I would do the following inserts:
insert into guy values('Mario', 'age', 18),('Mario', 'dollars', 31),('Mario', 'candies', 10);
and if I added the apples attribute, I would do the following inserts:
insert into guy values('John', 'apples', 3), ('Bob', 'apples', 5), ('Mario', 'apples' 0);
I hope I understand, thanks for all your time :)
Note: the guy table is a view and is the result of joining the three tables, so don’t worry that the database wasn’t normalized, also don’t tell me that the queries are incorrect (because I already know that), better tell me instead what I should change in them.
Edit (October 18, 2011, 10:08):
Maybe if there was a way to put all the attributes of the guys on one line ?, like this:
+------+-----------+-------+------+------------+--------+------+------------+--------+ | guy | attribute | value | guy | attribute | value | guy | attribute | value | +------+-----------+-------+------+------------+--------+------+------------+--------+ | John | age | 12 | John | dollars | 15 | John | candies | 3 | | Bob | age | 15 | Bob | dollars | 20 | Bob | candies | 6 | +------+-----------+-------+------+------------+--------+------+------------+--------+