Select where one field occurs in another field in another table.

[bskauge]

Hello,

I thought this should be easy, but I just can't figure it out.

I have two tables, and I want to list every record from table A where 'name' is mentioned in a list of names in table B.

I've tried something like
SELECT * FROM table_a WHERE name IN ('$table_b[listofnames]')
and
SELECT * FROM table_a WHERE name LIKE '%$table_b[listofnames]%'

This works when $table_b[listofnames] only contains one name, but there is a comma separated list of names in that field. I see that $table_b[listofnames] returns the whole list, so the wildcards won't work since they are put at the beginning and at the end of the list. How do I do this? And what should I eventually do about double fornames (like "John Henry")?

I really hope someone can help me out?



Kindest regards,
Bjorn Skauge
Norway

[xsist10]

SELECT * FROM table_a a, table_b b WHERE a.name = b.name AND b.field1 = "x"

What is the query you use to get the list from table_b?

[bskauge]

Hm, still don't get it.

What is the "a" in table_a a supposed to represent? I tried this, but couldn't get it to work.

Hoping for further help.



regards
Bjorn Skauge

[bskauge]

[quote]SELECT * FROM table_a a, table_b b WHERE a.name = b.name AND b.field1 = "x"[/quote]

a.name contains just one name, b.name is a long list of names. a.name = b.name will only match if b.name contains just one name. So I want to get all the records from table a where that one name from a.name is mentioned in the b.name list.

[bskauge]

Nevermind, I found the solution:

"SELECT * FROM table_a WHERE MATCH(name) AGAINST ('$table_b[listofnames]' IN BOOLEAN MODE) ORDER BY merke ASC"