Help with Join...

[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 but it just retrieved all the records in the db, regardless of the city:

SELECT contacts.doc_id 
         , contacts.city1
         , contacts.city2 
         , contacts.city3 
	 , contacts.phone
         , contacts.lastname
         , COALESCE(comments.notes, '') AS notes  

FROM contacts 
LEFT OUTER 
   JOIN comments 
   ON comments.doc_id = contacts.doc_id 
   AND (contacts.city1 LIKE 'timb%' OR contacts.city2 LIKE 'timb%' OR contacts.city3 LIKE 'timb%') 
ORDER BY contacts.lastname DESC, notes DESC

Any other suggestions?

[paulnaj]

Hi jj,

Use a WHERE clause instead of AND ... and I don't think you need the 'OUTER' ...

SELECT contacts.doc_id  

         , contacts.city1 
         , contacts.city2  

         , contacts.city3  

         , contacts.phone 
         , contacts.lastname 
         , COALESCE(comments.notes, '') AS notes   

FROM contacts  

LEFT JOIN comments ON (comments.doc_id = contacts.doc_id)
WHERE (contacts.city1 LIKE 'timb%' OR contacts.city2 LIKE 'timb%' OR contacts.city3 LIKE 'timb%')  

ORDER BY contacts.lastname DESC, notes DESC

That should do it!

Paul 🙂

[jjfletch]

w00t! That worked. 🙂

Thanks Paul!