How to find a record that does not exists from a table based on other table value

Question

So i have two tables, this one is books

and this one is payment

now i want to select if there are any records in books that have a similiar(select * from a like a.field like '%blabla%) title or even the same title but not exist in payment

i tried not exist but im not sure because the executing query process is very long so i thought it wasn't the case.

Most people here want sample table data _and the expected result_ as formatted text, not as images. — jarlh, Aug 18 '20 at 15:23
Even if *the executing query process is very long* this does not mean that the query is wrong. Post what you tried. — forpas, Aug 18 '20 at 15:24

tlk27 · Accepted Answer · 2020-08-18T16:19:51.537

Given the information, I have tried to put together an example. I hope this is helpful and gets you close to what you want.


CREATE TABLE books
    (`number` int, `verification_date` date, `title` varchar(6))
;
    
INSERT INTO books
    (`number`, `verification_date`, `title`)
VALUES
    (14116299, '2020-05-01 18:00:00', 'Title1'),
    (12331189, '2020-07-01 18:00:00', 'Title2'),
    (13123321, NULL, 'Title4'),
    (12318283, '2020-12-31 18:00:00', 'Title3'),
    (12318284, '2021-01-31 18:00:00', 'Title2')
;



CREATE TABLE payments
    (`number` int, `title` varchar(6), `pay_date` date)
;
    
INSERT INTO payments
    (`number`, `title`, `pay_date`)
VALUES
    (14116299, 'Title1', '2020-05-01 18:00:00'),
    (12318283, 'Title3', '2020-12-31 17:00:00')
;

We are selecting all columns from books and keeping only records that don't have a match in the payments table. More info on this: How to select rows with no matching entry in another table?. Then added an additional where clause to search the books table for titles.

SELECT b.*
FROM books b
LEFT JOIN payments p ON b.number = p.number
WHERE p.number is NULL
    AND b.title LIKE'%2'

Output:

number  verification_date   title
12331189    2020-07-01      Title2
12318284    2021-01-31      Title2

SQL Fiddle

How to find a record that does not exists from a table based on other table value

1 Answers1