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]
