[sqlite] Re: Re: Select with left outer join - Am I going mad ? Second edition

[Igor Tandetnik]

Alexandre Guion <[EMAIL PROTECTED]> wrote:
I tried this one of course, and it doesn't work, it could be a bug. It
returns every property for every medium (not just 'myprops')

I believe this should work (untested):

SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m LEFT JOIN
(MediumPropMap AS mpm
   JOIN Property AS p ON (mpm.PropID=p.PropID AND p.UserID=1)
   JOIN PropName AS pn ON
       (p.PropNameID=pn.PropNameID AND pn.Text='myprop')
   JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
) ON mpm.MediumID=m.MediumID

ORDER BY value
;


Or this:

SELECT m.MediumID AS id, IFNULL(x.Name, '') AS name, IFNULL(x.Value,
'') AS value
FROM Media AS m LEFT JOIN
(SELECT mpm.MediumID as MediumID, pn.Text AS Name, pv.Text AS Value
FROM MediumPropMap AS mpm
   JOIN Property AS p ON (mpm.PropID=p.PropID AND p.UserID=1)
   JOIN PropName AS pn ON
       (p.PropNameID=pn.PropNameID AND pn.Text='myprop')
   JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
) AS x ON x.MediumID=m.MediumID

ORDER BY value
;

Igor Tandetnik 


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------