MYSQL: Return any value on missing row.

Hi Guys,

I'm having problems returning a set of rows. What I have is two tables,
one to store properties and another storing the images (image paths) of the properties. Now a property isn't required to have an image. In that case I want to display an empty column or whatever (like null value). The thing is if a property doesn't have an image it's not listed in the images table. It's nonexistent.

I try to return all property adverts and each advert default image (only need the default one). I'm trying to achieve this in one SQL query below.

Code: ( text )

SELECT pa.property_id_pk,
pa.property_price,
pa.property_refno,
pa.property_bedrooms,
pa.property_description,
pa.property_featured,
pa.property_sale_rent,
pa.advert_active,
pa.advert_start_date,
pa.advert_end_date,
pim.image_url
FROM tb_property_advert as pa
INNER JOIN tb_property_image as pim
ON pa.property_id_pk=pim.property_advert_id_fk
AND image_default = 1

Obviously this will only return the property adverts with a FK in the images table. What I need is to return the rest of property adverts (which have no image in the image table) and have it show as an empty column or null column.

Hope I explained myself well enough. Any ideas appreciated
Atli I think this is one for you :P

Thanks a lot
Luk

pechar

Member

52 Posts

July 1st, 2008
08:07 AM
#2

Re: MYSQL: Return any value on missing row.

I Solved it!!! 2 mins after I posted here sorry!
All I had to do is a LEFT JOIN instead of an INNER JOIN as follows:

Code: ( text )

SELECT pa.property_id_pk,
pa.property_price,
pa.property_refno,
pa.property_bedrooms,
pa.property_description,
pa.property_featured,
pa.property_sale_rent,
pa.advert_active,
pa.advert_start_date,
pa.advert_end_date,
pim.image_url
FROM tb_property_advert as pa
LEFT JOIN tb_property_image as pim
ON pa.property_id_pk=pim.property_advert_id_fk
AND image_default = 1

Please correct me if I'm wrong but it seems to be giving me the results I want!

Thanks anyway
Luk