SQLite Forum

Mismatch in query results
Login

Mismatch in query results

(1) By Trudge on 2020-12-01 20:28:47 [source]

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] [source]

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] [source]

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] [source]

A couple of notes:

Although I think your definition for PRIMARY KEY 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 FOREIGN KEYs. 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] [source]

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!