CODEWITHSUNDEEP
mysqlsqldatabase
Jake Paul
Jake Paul

Reputation: 13

Mysql Query that gets records that don't belong to set

I have two tables (tb_book and tb_page).
Tb_page contains the summary of each page of a book. The fields in this table are page_number, summary, book_id.

page_number summary book_id
1 Summary book 1 1
2 Summary book 1 1
3 Summary book 1 1
4 Summary book 1 1
5 Summary book 1 1

tb_book contains a list of the pages that are available for a book. The fields in the table are book_id, book_name, start_page, end_page. As you can see, this table indicates a set of pages (start_page, end_page); however, you can have gaps between these sets.

book_id book_name start_page end_page
1 book 1 1 2
1 book 1 5 5

I have to do a query that gets all the pages that are not in the book sequences. (if there is more than 1 sequence per book, it has to be the difference). Using this example data, the records I should get are 3 and 4 because they don't belong to the sequence 1 to 2 and 5 to 5. Queries I'm using

Select page_number from tb_page where book_id = (parameter used #1);
Select start_page, end_page from tb_book where book_id = (parameter used #1);

Upvotes: 0

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

LEFT JOIN (on the right tables, of course) is a very reasonable solution.

However, NOT EXISTS is almost a direct translation of your question ("I have to do a query that gets all the pages that are not in the book sequences") into SQL:

select b.*
from tb_page p
where not exists (select 1
                  from tb_book b
                  where b.book_id = p.book_id and
                        p.page_number >= b.start_page and
                        p.page_number <= b.end_page
                 );

For either version, an index on tb_book(book_id, start_page, end_page) will help performance.

Upvotes: 0

Luuk
Luuk

Reputation: 14899

DROP TABLE IF EXISTS tb_book;
CREATE TABLE tb_book(page_number INT,   summary VARCHAR(20), book_id INT);
INSERT INTO tb_book VALUES
(1,     'Summary book 1',  1),
(2,     'Summary book 1',  1),
(3,     'Summary book 1',  1),
(4,     'Summary book 1',  1),
(5,     'Summary book 1',  1);

DROP TABLE IF EXISTS tb_page;
CREATE TABLE tb_page(book_id INT,       book_name VARCHAR(20),       start_page INT,     end_page INT);
INSERT INTO tb_page VALUES
(1,     'book 1',  1,       2),
(1,     'book 1',  5,       5);


SELECT tb_book.*
FROM tb_book
LEFT JOIN tb_page ON tb_page.book_id = tb_book.book_id
                 AND tb_book.page_number between tb_page.start_page and tb_page.end_page
WHERE tb_page.book_id is null
;

output:

+ ---------------- + ------------ + ------------ +
| page_number      | summary      | book_id      |
+ ---------------- + ------------ + ------------ +
| 3                | Summary book 1 | 1            |
| 4                | Summary book 1 | 1            |
+ ---------------- + ------------ + ------------ +

This query is, for every page in tb_book, trying to see if it is in a range defined in tb_page.

The where clause (WHERE tb_page.book_id is null) selects only those rows that do not have a range registered.

see also: DBFIDDLE

Upvotes: 1

Related Questions