Search code examples
mysqlsqlselectsubquerynot-exists

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

So i have two tables, this one is books

books

and this one is payment

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.

Solution

  • 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