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')
This one is tested and appears to work:
SELECT
m.MediumID AS id,
IFNULL(MAX(pn.Text), '') AS name,
IFNULL(MAX(CASE WHEN pn.Text IS NULL THEN NULL ELSE pv.Text END),
'') 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 m.MediumID ORDER BY value;
Each group produced by GROUP BY can have only one non-NULL value in
pn.Text (assuming each medium may have no more than one of each
property). MAX aggregate is simply used to pick up this single non-NULL
value, and the corresponding pv.Text
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
