MySQL - Join question

I have a real estate idx database with 3 tables res, seo, fagents.
fagents is a table containing info on agents from my company and no other company. I'm trying to populate a property detail page and if the property is listed by my company, I want to show additional details about the agent (from the fagents table)

Here is my query:

SELECT * FROM res,seo,fagents WHERE res.listID = "#URL.listID#" AND res.listID = seo.listID AND res.agentID = fagents.agentID

The problem with this query is that if the property is listed by another company then  res.agentID = fagents.agentID wont find a match and the property will not be shown.

How can I make this work? Any help appreciated!
Who is Participating?
_agx_Connect With a Mentor Commented:
Switch to an OUTER JOIN on the "fagents" table, so the query always returns the "res " record, even when there's no matching agent.

SELECT res.listID, ....other columns ...
FROM  res
          INNER JOIN seo ON res.listID = seo.listID
          LEFT JOIN fagents ON res.agentID = fagents.agentID
WHERE res.listID = <cfqueryparam value="#URL.listID#" cfsqltype="cf_sql_varchar">
Bang-O-MaticAuthor Commented:
when i finish this site, Im going to add "programming by _agx_" in the footer :)
Haha, glad to help where I can :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.