MYSQL: Return any value on missing row.

[pechar]

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.

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]

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:

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