Jump to content

Archived

This topic is now archived and is closed to further replies.

bskauge

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
Nevermind, I found the solution:

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.