Using data from two tables, help with join statement?

freiden · Joined: 03 Mar 2008 Posts: 1

I've been trying many combinations of things, but I think what I need is some kind of join statement. I have two tables, xyz_dest and xyz_destloc. I'm performing a query that needs to find all of the rows in xyz_destloc that have a specific zipcode column but also checks to see if the 'title' column of xyz_dest is similar to the search term.

The method I'm trying to accomplish is to perform that search, but then only display DISTINCT rows from xyz_dest that show all of the rows from xyz_destloc that are relevant.
For example

xyz_destloc:
id | zipcode | address | destid(id from table xyz_dest)
1 | 12345 | 123 way | 1
2 | 12345 | 412 street| 1
3 | 12344 | 123 xyz | 1
4 | 54321 | 415 abc | 2

xyz_dest:
id | title
1 | 'Destination 1'
2 | 'Destination 2'
3 | 'Destination 3'

So if I search for a title similar to 'destination' in the zipcode 12345, I would like it to return:

"Destination 1: 123 way & 412 street"

I hope that makes sense
Right now, my query is something like this - that does not function...
"SELECT xyz_dest.id, xyz_dest.title FROM xyz_dest, xyz_destloc ON xyz_destloc.destid=xyz_dest.id WHERE xyz_destloc.zipcode='$zipcode' AND xyz_dest.title LIKE '%$keywords%'"

Thanks a bunch!!!!

lostboy · Joined: 02 May 2004 Posts: 6033 Location: toronto, canada

SELECT xyz_dest.id, xyz_dest.title, xyz_destloc.address
FROM xyz_dest INNER JOIN xyz_destloc
ON xyz_destloc.destid=xyz_dest.id
WHERE xyz_destloc.zipcode='$zipcode'
AND xyz_dest.title LIKE '%$keywords%'

Then loop thru the results and format as desired