complex right outer join question

[AndyHopper]

I have 4 tables:

1.) property - contains property listings (i.e. address, price, city, state, etc); pid is an int and the primary key
2.) property_ext - contains more information about the property (i.e. a long description, etc); pid is an int and the primary key
3.) media_rels - contains relationships between media and properties; fields include:
pid (the property key), media_id (the media key), and [default] (specify this media_id as the default media file for this property)
4.) media - contains media information- type, path, etc; media_id is the key

So here are the relationships:
-property.pid = media_rels.pid
-property.pid = property_ext.pid
-media_rels.media_id = media.media_id

I'd like to select the property and exteneded property information and the best picture to display where the best picture is defined by media.type='image/pjpeg' and [default]=1

The query below accomplishes roughly what I'm trying to do, but it selects all the media. I need to select all properties and limit the media.

SELECT TOP 5 property.client_id,property_ext.title,property_ext.desc_short,property.remark1,property.pid,property.mls,property_ext.title AS title, property.town AS town, property.list_price AS list_price, property_ext.homepage, media.dir,media.original, property.mod_date_time
FROM media INNER JOIN media_rels ON media.media_id = media_rels.media_id RIGHT OUTER JOIN property INNER JOIN property_ext ON property.pid = property_ext.pid ON media_rels.pid = property_ext.pid WHERE (property.client_id = 1) ORDER BY property.mod_date_time


This series of logic more closely resembles my goal, however I haven't been able to figure out an appropriate way to join everything together while selecting only the rows I want from media and media_rels

SELECT *
FROM property_ext INNER JOIN
property ON property_ext.pid = property.pid WHERE property.client_id=1 ORDER by property.mod_date_time

THEN

SELECT TOP 1 * FROM media INNER JOIN media_rels ON media.media_id = media_rels.media_id
WHERE (media_rels.pid = [property id from above] WHERE media.type ='image/pjpeg') ORDER BY [default]

I would really appreciate any advice anyone has to offer!
Thanks in advance!

-Andy

 

[r937]

assuming that there is only one best picture per property, all you need is inner joins, with join conditions limiting which media row(s) you want

SELECT property.client_id
     , property_ext.title
     , property_ext.desc_short
     , property.remark1
     , property.pid
     , property.mls
     , property_ext.title AS title
     , property.town AS town
     , property.list_price AS list_price
     , property_ext.homepage
     , media.dir
     , media.original
     , property.mod_date_time
  FROM property
INNER 
  JOIN property_ext 
    ON property.pid = property_ext.pid 
INNER
  JOIN media_rels
    ON property.pid = media_rels.pid
INNER
  JOIN media 
    ON media_rels.media_id = media.media_id
   AND   media.type ='image/pjpeg'
   AND   media_rels.[default] = 1
 WHERE property.client_id=1

those two ANDs in the last join are sometimes written in the WHERE clause -- semantically they produce the same results, but i think in the ON condition they might perform better (i cannot really say, i am not a dba)

rudy

http://rudy.ca/

 

[AndyHopper]

Thanks for the prompt reponse rudy, however it won't solve my problem. My problem is some properties will not have a picture, but I still need to get the information about the property. Other properties will have may pictures, but the picture may not be specified as the default, in which I case I would like to select a the picture with the lowest media_id, because this would be the earliest picture entered for the property.

 

[sunila7]

Hi,

Try this query


SELECT P.*,PE.*, M.*
FROM Property P
Inner Join Property_ext PE ON P.pid =PE.Pid
Left Outer Join Media_rels MR ON MR.pid=PE.pid and [default] = 1
Inner Join Media M ON MR.media_id = M.media_id and M.type = 'image/pjpeg'

Sunil

 

[r937]

"some properties will not have a picture" means LEFT OUTER JOIN as sunil has shown

"but the picture may not be specified as the default, in which I case I would like to select the picture with the lowest media_id" is a new requirement not previously mentioned which makes the whole problem so much more complex

are you sure at most one picture will be the default, if any? is there any way you can program your application to always have a default if there are any pictures?

right now i think you're looking at a couple of separate queries combined with UNION