get rows that have a filed in common with another row

[andrej h]

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)

Thanks,

andrej

[Bill Karwin]

andrej h wrote:

"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)

Subqueries work in MySQL 4.1, but not in earlier versions.

It seems like if you can use subqueries, this might do what you want:

SELECT * FROM t WHERE id IN (SELECT DISTINCT id FROM t WHERE VALUE LIKE
'%string2%')

If you can't use subqueries I think this works:

SELECT DISTINCT t1.* FROM t AS t1 INNER JOIN t AS t2 ON t1.id = t2.id
WHERE t2.value LIKE '%string2%'

Regards,
Bill K.

[andrej h]

Bill Karwin <bi**@karwin.com> wrote in message news:<co*********@enews3.newsguy.com>...

If you can't use subqueries I think this works:

SELECT DISTINCT t1.* FROM t AS t1 INNER JOIN t AS t2 ON t1.id = t2.id
WHERE t2.value LIKE '%string2%'

Turns out I can't use subqueries after all but this really wokrs!
However it'll take a while untill I understand all of it, but that's
my homework.

Thanks a lot,

andrej