Mismatch in query results
(1) By Trudge on 2020-12-01 20:28:47 [link] [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 [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 KEY
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] [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!