Hey all,
I'm getting crazy on a problem with left join in SQLite. Either I didn't
understand something, or there's a bug somewhere.
Here's a sample of my DB
CREATE TABLE Media (
MediumId INTEGER PRIMARY KEY,
...
)
CREATE TABLE MediumPropMap (
MediumId INTEGER NOT NULL, (foreign key from Media)
PropId INTEGER NOT NULL, (foreign key from Property)
UNIQUE (MediumId, PropId)
)
CREATE TABLE Property (
PropId INTEGER PRIMARY KEY,
PropValueId INTEGER NOT NULL, (foreign key from PropValue)
PropNameId INTEGER NOT NULL, (foreign key from PropName)
UserId INTEGER NOT NULL
)
CREATE TABLE PropValue (
PropValueId PRIMARY KEY,
Text TEXT
)
CREATE TABLE PropName (
PropNameId PRIMARY KEY,
Text TEXT
)
Now what I'm trying to do is to get an SQL query that would return all
the media sorted by one of their property (or few properties at the same
time), *taking into account that not all the media have that property*.
The answer is, of course, to use left joins, and my first attempt was :
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 ?
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
;
Not right either, returns now the correct count of media, but none of my
properties that I'm looking for.
Another attempt, trying to get things simpler :
SELECT mpm.MediumID AS id, IFNULL(pv.Text,'') as value
FROM Media AS mpm
INNER JOIN PropName AS pn ON pn.Text='rating'
LEFT JOIN Property AS p ON (mpm.PropID=p.PropID AND
p.PropNameID=pn.PropNameID AND p.UserID=1)
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
GROUP BY id
ORDER BY value
;
Now the result is really far from what I'm looking for. Things are
getting to get weird here...
I finally found one that was working :
SELECT m.MediumID AS id, IFNULL(pn0.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON ( mpm.MediumID=m.MediumID AND
mpm.PropID IN
(SELECT p.PropID FROM Property AS p, PropName AS pn WHERE
pn.Text='myprop' AND p.PropNameID=pn.PropNameID)
)
LEFT JOIN Property AS p ON mpm0.PropID=p.PropID
LEFT JOIN PropName AS pn ON p.PropNameID=pn.PropNameID
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
GROUP BY id
ORDER BY value
;
But this one gets really long when used on a property that has many
different values in the DB, I believe sqlite is here creating an
intermediate result table and therefore get things really long to process.
Any other ideas ? And can someone explain why the first attempts weren't
working ?
Thanks,
- Alex
