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')
Igor Tandetnik wrote:
Alexandre Guion <[EMAIL PROTECTED]> wrote:
SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
LEFT JOIN Property AS p ON mpm.PropID=p.PropID
LEFT JOIN PropName AS pn ON p.PropNameID=pn.PropNameID
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
WHERE pn.Text='myprop' AND p.UserID=1
GROUP BY id ORDER BY value
;
This doesn't work, as I could see in other threads. It returns only
the media that have the property 'myprop', apparently the where
clause is applied on the global result, but why ?
Because that's how SQL works. What did you expect?
anyway, second attempt :
SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
LEFT JOIN PropName AS pn ON (pn.Text='myprop')
LEFT JOIN Property AS p ON (p.PropNameID=pn.PropNameID AND
mpm.PropID=p.PropID AND p.UserID=1)
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
GROUP BY id
ORDER BY value
;
Do joins in the same order you did in the first query, just move
conditions from WHERE to ON:
SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
LEFT JOIN Property AS p ON (mpm.PropID=p.PropID AND p.UserID=1)
LEFT JOIN PropName AS pn ON
(p.PropNameID=pn.PropNameID AND pn.Text='myprop')
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
GROUP BY id ORDER BY value
;
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
