1

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 kind of condition in its attributes and values?

Basically I want to be able to search guys and its attributes using all kinds of conditions like gt, lt, ge, le, eq, ne, between, etc.. and also be able to group these conditions by "ands" and "ors".

For example, lets say I want to get the guys that 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 that is just a simple query, the queries could get as complicated as this one

//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 is returned

Here is the code to create the table and add the 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);

Have in mind that I could later add more more guys and more attributes, but all guys will always have the same number of attributes, for example, if I were to add 'Mario', I would do the following inserts:

insert into guy values('Mario', 'age', 18),('Mario', 'dollars', 31),('Mario', 'candies', 10);

and if I were to add the attribute 'apples' I would do the following inserts:

insert into guy values('John', 'apples', 3), ('Bob', 'apples', 5), ('Mario', 'apples' 0);

I hope I made myself understandable, thank you for all your time :)

Note: the guy table is a view and is the result of joining 3 tables, so don't worry about the database not been normalized, also please don't tell me that the queries are wrong (cause I already know that), instead, better tell me what should I change in them.

Edit (Oct 18 2011, 10:08 AM):

Maybe If there were a way to put all of each guys attributes in one row?, 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 |
+------+-----------+-------+------+------------+--------+------+------------+--------+
ilovelamp
  • 739
  • 3
  • 9
  • 20
  • I kinda rephrase the questions with more information and hopefully more understandable, please go check it out: http://stackoverflow.com/questions/7815323/search-in-grouped-columns-in-mysql – ilovelamp Oct 19 '11 at 00:37

2 Answers2

2

to get the guys that have more than 16 dollars or have less than 5 candies, use a self-join:

SELECT g1.guy FROM guy g1
INNER JOIN guy g2 ON (g1.guy = g2.guy)
WHERE (g1.attribute = 'dollars' AND g1.value > 16)
   OR (g2.attribute = 'candies' AND g2.value < 5)
imm
  • 5,837
  • 1
  • 26
  • 32
  • Thx for you fast reply :) What if I want to search a guy by all his attributes?, I would need to do a self-join for each attribute? that's the kind of query that I a want to avoid because there are guy that have more than 30 attributes and I am not sure if there would be a performance issue, please correct me if I'm wrong. – ilovelamp Oct 18 '11 at 14:58
  • @rhinojosa, yes, you'd have to join in each one. but in your post you said that this is a view and is already the result of a number of joins. why not instead query the original data, which might be easier to do what you want with a simpler query? – imm Oct 18 '11 at 19:27
  • I'll publish the schema of all tables, do you think I should do this in a new post? I'm kinda new to stackoverflow – ilovelamp Oct 18 '11 at 20:28
  • @rhinojosa, I agree, it probably would be a good idea to start a new question. you can reference this question in your new question. – imm Oct 18 '11 at 21:35
  • I kinda rephrase the questions with more information and hopefully more understandable, please go check it out: http://stackoverflow.com/questions/7815323/search-in-grouped-columns-in-mysql – ilovelamp Oct 19 '11 at 00:36
0

Try this code..

select guy,attribute,`value`
  from guy
having (attribute = 'dollars' and value > 16) or (attribute = 'candies' and value < 5);
Sam Casil
  • 938
  • 1
  • 11
  • 16
  • Thx for you answer :) What if I want to get the guys that have both more than 16 dollars and whose age is greater than 14 (like Bob)?, this does not work :( select guy,attribute,`value` from guy having (attribute = 'dollars' and value > 16) and (attribute = 'age' and value > 14); – ilovelamp Oct 18 '11 at 15:08
  • Try to change AND to OR.. SELECT guy,attribute,value FROM guy HAVING (attribute = 'dollars' and value > 16) **OR** (attribute = 'age' and value > 14); – Sam Casil Oct 19 '11 at 02:17
  • thanks for the reply :), what if I need to get the guys that have exactly an age of 12, 15 dollars and **4** candies, John does not have 4 candies, but this query still shows John as a result when it shouldn't `code`SELECT guy,attribute,value FROM guy having (attribute = 'age' and value = 12) or (attribute = 'dollars' and value = 15) or (attribute = 'candies' and value = 4);`code` maybe with some where clause we could do something, something like this `code`WHERE count(*) = 3`code` but what if I want to use an "OR" clause? – ilovelamp Oct 19 '11 at 02:36
  • 4 candies?there's no 4 candies in your table. – Sam Casil Oct 19 '11 at 02:43
  • Yup, that is the idea, the 4 candies condition is not true, so John should be excluded from the result but it isn't :s – ilovelamp Oct 19 '11 at 02:53
  • Change it to... SELECT guy,attribute,value FROM guy HAVING (attribute = 'age' and value = 12) AND (attribute = 'dollars' and value = 15) AND (attribute = 'candies' and value = 4); – Sam Casil Oct 19 '11 at 03:36
  • Using **AND** is when your query needs more validations and using **OR** means either one condition is true you will get the value. – Sam Casil Oct 19 '11 at 03:38