andrej h wrote:
Hello, I'm new to mysql (and databases, fo that matter) and have problems finding a solution. The situation is as follows: I have one table with filtered information about users, the columns are, say, id, field and value. As you can see, any id can have more rows assiciated with him, each containing one crucial piece of information. Now I want to search the values for something and return all rows with the same id -- in effect this means that I cannot use "select * from t where value like ... ", which does, of course, return all rows that match the expression rather than all rows with the same id that happened to also contain a value with the expression.
If I'm not clear enough, here's an example:
id field value --|-----|------- a x string1 a y string2 b x string3 a z string4
"select * from t where value like string2" returns the second row, but I want to return all rows with the same id as in row number two.
I've tried this subquery, to no avail: select * from t where id = (select id from t where value like '%string2%' limit 1)
You would make it easier for us to help you if you would tell us what happened. Did you get an error message? If so, paste it in. Did you get unexpected results? If so, show us what you expected and what you got. "Doesn't work", or its equivalent, "tried ... to no avail", doesn't provide much for us to go on.
Thanks,
andrej
Subqueries require mysql 4.1. Perhaps you have an older version. Try this:
SELECT t2.* FROM t t1 JOIN t t2 ON t1.id = t2.id WHERE t1.value = 'string2';
+------+-------+---------+ | id | field | value | +------+-------+---------+ | a | x | string1 | | a | y | string2 | | a | z | string4 | +------+-------+---------+ 3 rows in set (0.00 sec)
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
