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