2

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).

3
  • 2
    Why isn't Produkt4 in the results? It has a status of 1. Dec 30, 2017 at 20:12
  • There's a typo in the product insert from the SQLFiddle. One of the rows has the name "product2" instead of "Produkt2".
    – RDFozz
    Dec 31, 2017 at 0:13
  • 1
    Based on the last paragraph of the post, the only products that should appear are ones that have both a row with revision_status = 1, and a row where revision_status is NULL.
    – RDFozz
    Dec 31, 2017 at 0:19

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.