SQLite Forum

Mismatch in query results
Login

Mismatch in query results

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

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

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!