Jump to content


Photo

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


  • Please log in to reply
4 replies to this topic

#1 bskauge

bskauge
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 18 October 2006 - 01:15 PM

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

#2 xsist10

xsist10
  • Members
  • PipPipPip
  • Advanced Member
  • 114 posts

Posted 18 October 2006 - 01:23 PM

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?
SA PHP Archives - www.phparchives.za.org

#3 bskauge

bskauge
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 18 October 2006 - 08:57 PM

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

#4 bskauge

bskauge
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 19 October 2006 - 08:03 AM

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


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.

#5 bskauge

bskauge
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 19 October 2006 - 10:09 AM

Nevermind, I found the solution:

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users