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

2006-12-14 Thread Igor Tandetnik

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



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

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



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

2006-12-13 Thread Igor Tandetnik

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