On 19/08/2007 4:01 AM, Jef Driesen wrote:
Suppose I have two related tables:CREATE TABLE events ( id INTEGER PRIMARY KEY NOT NULL, place_id INTEGER ); CREATE TABLE places ( id INTEGER PRIMARY KEY NOT NULL, name TEXT ); INSERT INTO places (id, name) VALUES (1, 'Place A'); INSERT INTO places (id, name) VALUES (2, 'Place B'); INSERT INTO places (id, name) VALUES (3, 'Place C'); INSERT INTO events (id, place_id) VALUES (1, 1); INSERT INTO events (id, place_id) VALUES (2, 2); INSERT INTO events (id, place_id) VALUES (3, 1); Now, I want to count the number of 'events' at every 'place'. I started with a simple join and a group by clause to perform the count: SELECT name, count (*) AS count FROM events LEFT JOIN places ON places.id = events.place_id GROUP BY events.place_id; name|count Place A|2 Place B|1 It executes very fast (because the join can take advantage of the primary key) but does not produce the desired result. As you can see, not all places are included in the result: So I changed swapped the tables in the join: SELECT name, count (events.place_id) AS count FROM places LEFT JOIN events ON events.place_id = places.id GROUP BY places.id; name|count Place A|2 Place B|1 Place C|1
Folks are dumb where I come from; can someone please explain how this could be correct? The INSERT statements create 2 events at place_id 1 (A), 1 event at place_id 2 (B) and *ZERO* events at place_id 3 (C).
Please pardon me if this is strange behaviour (I'm new to this mailing list) but I actually *ran* the OP's code, with this result:
C:\junk>sqlite3 junk.sq3 SQLite version 3.4.2 Enter ".help" for instructions sqlite> .read outer_join_query.sql --- query 1 --- Place A|2 Place B|1 --- query 2 --- Place A|2 Place B|1 Place C|0 --- query 3 --- Place A|2 Place B|1 Place C|0 sqlite> Cheers, John ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
