CODEWITHSUNDEEP
mysqlsql
takeone
takeone

Reputation: 37

MySQL Query to discard records based on their status on a second table

I have two tables:

CREATE TABLE qr_code (
    id   int(10) primary key auto_increment,
    code int(10) not null
);

CREATE TABLE area_qrcode (
    id         int(10) primary key auto_increment,
    area_id    int(10) not null,
    qr_code_id int(10) not null,
    status     set('open', 'close')
);

In my tables, I have the following records:

-- qr_code
+----+------+
| id | code |
+----+------+
|  1 |  200 |
|  2 |  201 |
|  3 |  202 |
+----+------+

-- areaqr_code
+----+---------+------------+--------+
| id | area_id | qr_code_id | status |
+----+---------+------------+--------+
|  1 |       2 |          1 | open   |
|  2 |       4 |          3 | close  |
+----+---------+------------+--------+

I want to select from table qr_code all records that are not in status open in the area_qrcode table. qr_code.id corresponds to area_qrcode.qr_code_id.

A good result set would be:

+----+------+
| id | code |
+----+------+
|  2 |  201 |
|  3 |  202 |
+----+------+

I'm using the following query, but it's not working as I expect because if the table area_qrcode has no values corresponding to the values in qr_code, then I get no results for those records:

SELECT
    qr.*
FROM
    `qr_code` as qr,
    `area_qrcode` as aq 
WHERE
    (
        aq.qr_code_id = qr.id and
        aq.status != 'open'
    )
    OR
    (
        aq.qr_code_id != qr.id
    );

What can I change to get records from qr_code even if there are not related records in areaqr_code?

Upvotes: 2

Views: 85

Answers (4)

Robby Cornelissen
Robby Cornelissen

Reputation: 97302

If I understood your question correctly, this is the query that you're looking for:

SELECT q.id, q.code
FROM qr_code q
LEFT JOIN area_qrcode aq
ON aq.qr_code_id = q.id
WHERE (aq.status IS NULL OR aq.status <> 'open')

Will result in:

ID  CODE
--  ----
2   201
3   202

Fiddle available here.

Upvotes: 0

VMai
VMai

Reputation: 10336

You can get your result with a subselect:

SELECT 
    * 
FROM 
    `qr_code` as qr
where 
    qr.id NOT IN (
        SELECT qr_code_id 
        FROM area_qrcode 
        WHERE status = 'open'
    );

Demo

With a LEFT JOIN you can get your result too:

SELECT 
    qr.* 
FROM 
    qr_code as qr 
LEFT JOIN 
    area_qrcode as aq 
ON
    qr.id = aq.qr_code_id
AND
    aq.status = 'open'
WHERE
    aq.qr_code_id IS NULL;

Updated Demo

A third possibility (not so intuitive as the first one):

SELECT 
    *
FROM 
    `qr_code` as qr
WHERE NOT EXISTS (
    SELECT
        1
    FROM
        area_qrcode
    WHERE
        status = 'open'
    AND
        qr_code_id = qr.id
);

Demo

Upvotes: 1

serakfalcon
serakfalcon

Reputation: 3531

Use a proper join, that will prevent confusion. 

SELECT qr_code.id,qr_code.code 
FROM qr_code 
LEFT JOIN area_qrcode 
ON area_qrcode.qr_code_id = qr_code.id
WHERE area_qrcode.status <> 'open' OR area_qrcode.status IS NULL

fiddle

Upvotes: 0

rahulmishra
rahulmishra

Reputation: 621

SELECT qr.* FROM qr_code as qr LEFT JOIN area_qrcode as aq ON aq.qr_code_id=qr.id and aq.status!='open' OR(aq.qr_code_id!=qr.id)

Upvotes: 0

Related Questions