I don't know how sqlite handles parenthesis in joins. the first query is not recognized by the syntax (mpm.MediumID undefined). The second one just returns ids with name and value being empty. However I only use 3.3.4, is there any documentation on this ?

Igor Tandetnik wrote:

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]
-----------------------------------------------------------------------------


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

Reply via email to