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

2006-12-13 Thread Alexandre Guion
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]
-



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

2006-12-13 Thread Igor Tandetnik

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