CODEWITHSUNDEEP
mysqlsql
Yehor Tiurin
Yehor Tiurin

Reputation: 23

MySQL: SELECT field from table1 not present in table2

Simplified my database looks like this:

CREATE
TABLE vinyls (
    id_vinyl INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,                  
    PRIMARY KEY (id_vinyl),
    UNIQUE KEY id_vinyl (id_vinyl));

CREATE
TABLE orders (
    id_order INT(10) UNSIGNED NOT NULL,                               
    PRIMARY KEY (id_order),
    UNIQUE KEY id_order (id_order));  

CREATE
TABLE orders_vinyls (
    id_order INT(10) UNSIGNED NOT NULL,
    id_vinyl INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (id_order, id_vinyl),
    UNIQUE KEY id_vinyl (id_vinyl),
    FOREIGN KEY (id_order) REFERENCES orders (id_order) ON DELETE CASCADE,
    FOREIGN KEY (id_vinyl) REFERENCES vinyls (id_vinyl) ON DELETE CASCADE);

It's designed to have one order containing several vinyls - table orders_vinyls. I need to get vinyls which are available for a customer. Those are the ones with id_vinyl not in table orders_vinyls. And there might be a situation when orders_vinyls is empty which causes some difficulties for me.

I would appreciate your help guys.

UPDATE: For instance table vinyls: (1), (2), (3), (4), (5), (6);

table orders: (1), (2), (3)

table orders_vinyls: (1, 1), (1, 2), (2, 3), (3, 4)

So I need to get vinyls with id_vinyl 5 or 6 because they are not in table orders_vinyls.

Upvotes: 1

Views: 435

Answers (3)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

You can use NOT EXISTS

Select * from table1 a
Where not exists (select 1 from table2  b where b.vynil_id = a.vynil_id)

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

If what you want is to select those vinyls not present in any order made, then one option is to use NOT EXISTS:

SELECT v.*
FROM vinyls AS v
WHERE NOT EXISTS (SELECT 1
                  FROM orders_vinuls AS ov 
                  WHERE ov.id_vinyl = v.id_vinyl)

This will also work in case table orders_vinuls is empty.

Upvotes: 2

Stefano Zanini
Stefano Zanini

Reputation: 5926

If I understand your question correctly, you can just use the set difference operation except

select id_vinyl from vinyls
except
select id_vinyl from orders_vinyls

If your actual vinyls table is more complex, you can use the result of the query above joined with the original table, or you can use a different query

select  *
from    vinyls
where   id_vinyl not in (
            select id_vinyl from orders_vinyls
        )

Upvotes: 0

Related Questions