Re: [sqlite] Re: Re: Select with left outer join - Am I going mad ? Second edition
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] -
Re: [sqlite] Re: Select with left outer join - Am I going mad ? Second edition
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] Select with left outer join - Am I going mad ? Second edition
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
Re: [sqlite] IF EXISTS when using DROP TABLE
You can use the PRAGMA query to test if a table exists or not. "PRAGMA table_info(`tablename`)" -- Alex Guion Software Engineer OrbNetworks, Inc. www.orb.com [EMAIL PROTECTED] wrote: Thank you. My version does not. I had used one of the archives to search for this issue, but the thread I found did not have an answer to the question. -- Eric Pankoke Founder / Lead Developer Point Of Light Software http://www.polsoftware.com/ -- Original message -- From: "Adriano Ferreira" <[EMAIL PROTECTED]> On 10/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: If I use the syntax "DROP TABLE tablename" everything is good, except of course I get a "no such table" error if the table does not exist. However, if I use the syntax "DROP TABLE IF EXISTS tablename", then I get the following error: 'near "EXISTS": syntax error'. Anyone have a thought as to what's going on? Is this a known issue? It looks like the same issue on the thread "Trouble with ALTER TABLE/ADD", answered by drh. Are you sure your version of SQLite supports "DROP TABLE IF EXISTS"? The URL http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql says IF EXISTS function, e.g. "DROP TABLE IF EXISTS temp;" Added in 3.3 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
