Hi,
I could use the inner join for the "entrys" join and the "items" join
but not the "entry-items" join because each entry can have more than
one item.
WITH a(id, name) AS (VALUES (1, 'A')),
b(id, name) AS (VALUES (1, 'B1'), (1, 'B2'))
SELECT * FROM a INNER JOIN b USING (id);
1|A|B1
1|A|B2
The only difference between inner and outer joins is how rows without
any match are handled.
Thanks Clemens! In my case entries can have zero items as well and I still
want the entry itself to show up.
I started with an OUTER JOIN as I find it easier to show that it's
doing the correct thing because I can search the output for errant
NULLs. Trying to detect missing rows in an INNER JOIN is harder.
If the join columns have the same name, using USING is easier.
And it would be a good idea to enforce the relationships between the
tables with foreign key constraints:
<https://www.sqlite.org/foreignkeys.html>
(However, constraints do not affect how you have to write your queries.)
Ah yes. It might be worth looking at this. I've always avoided it in the
past because my experience with other engines taught me that it makes
experimenting at the monitor harder. Are there any efficiency benefits or
is it just there to enforce data integrity (very important, of course;-))?
It looks like they have to be enabled on a per connection basis. In this
case I (currently) control all the client code but is it possible for the
foreign key relationships to get out of sync if one of the connections
omits to apply the pragma?
Thanks for the tips!
Regards,
@ndy
--
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
0x7EBA75FF
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
