Simple Two Table Join - Doubled results

[sgtpepper]

I am trying to get data from two different tables. My query is this

mysql_query("CREATE TEMPORARY TABLE temp_featured select residential.Id
,residential.photo_file,featured_listings.featured_listing,residential.remark1,residential.type
,featured_listings.agents_comments,residential.status from residential
,featured_listings where residential.lister_office_name='$client_name'");

Basically, I want most data from the residential table...there is one piece of data I need out of the featured_listings table. The problem is my results get doubled.

What can you see that I am doing wrong with the above query?

[petitduc]

You query two tables but don't join them.
So you probably get a cartesian product of the 2 tables which is generally a meaningless result.

Try to add a condition in your where clause :

WHERE .... AND featured_listings.id = residential.id

or something like that.

[sgtpepper]

Well, what I am doing is this:

Table Residential has a list of real estate properties
Table featured_listings is a new table. This table has field called Id, featured_list, and agents comments.

I am trying to figure out how I can get a list of results from the residential table....but have the values from featured_listings.featured_list show up in my list.

See, featured_listings.featured_list is going to contain a value either Y or N that turns the property on or off as a 'featured listing' in my php code.

Doing this

select residential.Id,residential.photo_file,featured_listings.featured_listing,residential.remark1,residential.type,featured_listings.agents_comments,residential.status from residential, featured_listings

Gives me doubled results in my list.

Keep in mind that the featured_listings is never going to contain all the Id in my residential table....only a select few. So, I have a default have of 'NO' for featured_listings.featured_list.

So, to sum it all up....I am trying to get results that include the value of featured_listings.featured_list for the specific Id.

I hope I explained what I am trying to do. So if I add AND featured_listings.id = residential.id like you suggested above, I am going to loose all the data that doesnt match id's

[weevil]

It sounds like you need a LEFT JOIN
http://www.mysql.com/doc/en/LEFT_JOIN_optimisation.html

Something like

SELECT * FROM residential LEFT JOIN featured_listings ON residential.id = featured_listings.id