Mismatch in query results
(1) By Trudge on 2020-12-01 20:28:47
I have a DB with tables 'artists' and 'cds' to manage my music collection. But I get 2 different results depending on these 2 queries: select count(*) from cds where genre like '%jazz%'; 1103 select count(*) from cds inner join artists on artists.artistid=cds.artistid and cds.genre like "%jazz%"; 1101 Schema: CREATE TABLE IF NOT EXISTS "artists" ( `id` integer NOT NULL, `artistid` integer, `name` text, PRIMARY KEY(`id` AUTOINCREMENT) ); CREATE TABLE IF NOT EXISTS "cds" ( `id` integer, `artistid` integer, `cdid` INTEGER, `title` text, `runtime` text, `notes` text, 'genre' text, PRIMARY KEY(`id` AUTOINCREMENT) ); How can I determine which 2 records are missing?
(2) By Keith Medcalf (kmedcalf) on 2020-12-01 20:49:25 in reply to 1 [link]
Pretty simple direct translation: ``` select cds.* from cds where genre like '%jazz%' EXCEPT select cds.* from cds inner join artists on artists.artistid=cds.artistid and cds.genre like "%jazz%"; ``` or directly from an English problem statement: ``` select * from cds where genre like '%jazz%' and artistid not in (select artistid from artists); ```
(3) By Trudge on 2020-12-01 22:28:20 in reply to 2 [link]
Excellent! Thank you very much. I need to get more familiar with SQL obviously. sqlite> select cds.* from cds where genre like '%jazz%' ...> EXCEPT ...> select cds.* from cds inner join artists on artists.artistid=cds.artistid and cds.genre like "%jazz%"; 20801|1697|1|Long Ago And Far Away|1:11:49||Jazz 20915|1759|1|Water From An Ancient Well (1985)|0:46:46||Jazz I'm missing those 2 artistid's.
(4.1) By Simon Slavin (slavin) on 2020-12-02 12:08:45 edited from 4.0 in reply to 3 [link]
A couple of notes: Although I think your definition for <code>PRIMARY KEY</code> will work fine, there are reasons why you might want to define those values slightly differently. I recommend you take a look at <https://sqlite.org/faq.html#q1> Also you can enforce the parent / child relationship using <code>FOREIGN KEY</code>s. That way your database will not allow such things to happen: <https://sqlite.org/foreignkeys.html> However, your data structure is good and you seem to understand how to proceed, so perhaps you're okay without it.
(5) By Trudge on 2020-12-02 16:30:12 in reply to 4.1 [link]
You are correct - I would rather PREVENT this from happening if possible, rather than keep correcting the fault. In fact I have 2 other tables with the same problem. Thinking UPSTREAM! Thanks for the tips eh!