Need Help with Join Clause

[jjfletch]

I've got 2 tables: contacts and comments. Each record in
contacts has a field_id (unique) that corresponds to field_id (not
unique) in comments.

This is what I want to do:

Find records with in contacts and comments where the field_id fields match, and where the city field in contacts contains "Timbuktu" and sort the results first by the notes field in comments and then by the city field in contacts

So, basically if I search for records in Timbuktu, I want to see all the records from contacts where the city contains "Timbuktu" but, I want to display the "Timbuktu" records that have comments associated with it first and those without last.

I've been looking at "JOIN" clauses, but I can't seem to figure out the syntax... It either doesn't work, or displays results strangely...

I tried the following, and it displayed all the records where the city matched "Timbuktu", but inserted content from the notes field into all the records (even those that didn't have any)

select * from contacts, comments where contacts.field_id =  comments.field_id or contacts.city1 like 'timb%' or contacts.city2  like 'timb%' or contacts.city3 like 'timb%' order by  contacts.lastname;

Can anyone help? Thanks!

 

[steven290]

try

select contacts.*
from contacts
inner join comments
on contacts.field_id=comments.field_id
where contacts.city1 like 'timb%'
or contacts.city2 like 'timb%'
or contacts.city3 like 'timb%'
order by contacts.lastname;

 

[ericbrunson]

To point out what was wrong with your original query, compare to this:

select * 
from contacts, comments 
where contacts.field_id =  comments.field_id 
and ( contacts.city1 like 'timb%' 
      or contacts.city2  like 'timb%' 
      or contacts.city3 like 'timb%' )
order by  contacts.lastname;

You *need* the field ids to match.