Mysql – Filter records that have a certain status

MySQL

I am using mysql and I have two tables, product and revision.

Joining them gives me the following table:

SELECT *
FROM product AS p
INNER JOIN revisions AS r ON p.revision_id = r.id 
ORDER BY p.id;

| id | name     | prices | revision_id | id | revision_status |
|----|----------|--------|-------------|----|-----------------|
| 1  | Produkt1 | 10     | 1           | 1  | 1               |
| 2  | Produkt1 | 4      | 2           | 2  | 0               |
| 3  | Produkt1 | 2      | 3           | 3  | null            |
| 4  | Product2 | 42     | 4           | 4  | 1               |
| 5  | Produkt2 | 43     | 5           | 5  | null            |
| 6  | Produkt2 | 78     | 6           | 6  | 0               |
| 7  | Produkt3 | 1      | 7           | 7  | 1               |
| 8  | Produkt3 | 3      | 8           | 8  | null            |
| 9  | Produkt3 | 4      | 9           | 9  | null            |
| 10 | Produkt4 | 33     | 10          | 10 | 1               |

A Product has a unique ID that identifies a product. Due to price changes each product can have several different prices. Each product has a revision id.

The revision table has a revision_status and can have three different values: 1 for approved price, 0 for closed revision and null for to be revised.

As a final result I would like to get all records that have revision_status = 1 and also a revision_status = null.

Final result

| id | name     | prices | revision_id | id | revision_status |
|----|----------|--------|-------------|----|-----------------|
| 1  | Produkt1 | 10     | 1           | 1  | 1               |
| 3  | Produkt1 | 2      | 3           | 3  | null            |
| 4  | Product2 | 42     | 4           | 4  | 1               |
| 5  | Produkt2 | 43     | 5           | 5  | null            |
| 7  | Produkt3 | 1      | 7           | 7  | 1               |
| 8  | Produkt3 | 3      | 8           | 8  | null            |
| 9  | Produkt3 | 4      | 9           | 9  | null            |

I tried the following:

SELECT *
FROM product AS p
INNER JOIN revisions AS r ON p.revision_id = r.id 
WHERE p.name IN (
  SELECT p.name
  FROM product AS p
  INNER JOIN revisions AS r ON p.revision_id = r.id 
  GROUP BY p.name
  HAVING COUNT(r.revision_status) > 1)
ORDER BY p.id;

However, this also gives me revisions back that have a revision_status = 0

I created this sqlfiddle as a minimum viable example.

1 Product can have 1 Revision. Initially a product has revision_status of null. After it gets checked the status is set either to 1 – approved – or to 0 – not approved. In the application I have a logic, that only 1 product – with the same name – can have revision_status 1. I am looking for a way to show all revisions that have revision_status 1 and null and display the 1ns always on top of the nulls. (Like shown in the result table).

Best Answer

Try the following query (for starters):

select
  P.id
, P.name
, P.prices
, R.revision_status
, R.id
from Product P
  join Revisions R on P.revision_id = R.id
where R.revision_status <> '0'
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name
    having Product.name = P.name
  ) > 1
order by P.id, R.revision_status ;

--
-- result
-- "name" of the Product with the ID 4 changed to: "Produkt2"
-- before executing the query
--
+------+----------+--------+-----------------+------+
| id   | name     | prices | revision_status | id   |
+------+----------+--------+-----------------+------+
| 1    | Produkt1 | 10     | 1               | 1    |
| 3    | Produkt1 | 2      | null            | 3    |
| 4    | Produkt2 | 42     | 1               | 4    |
| 5    | Produkt2 | 43     | null            | 5    |
| 7    | Produkt3 | 1      | 1               | 7    |
| 8    | Produkt3 | 3      | null            | 8    |
| 9    | Produkt3 | 4      | null            | 9    |
+------+----------+--------+-----------------+------+
7 rows in set (0.01 sec)

-- MySQL 5.7

If this gives you the required result, maybe we should cover the (revision_status) cases '1' and 'null' explicitly, by refactoring the WHERE clause as follows:

select ... from ... -- same as above
where R.revision_status <> '0'
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name, Revisions.revision_status
    having Product.name = P.name and Revisions.revision_status = '1'
  ) >= 1
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name, Revisions.revision_status
    having Product.name = P.name and Revisions.revision_status = 'null'
  ) >= 1
order by P.id, R.revision_status ;

-- ... = 'null' due to your test data, maybe this needs IS NULL in reality.
Related Question