How to select rows with corresponding fields in MySQL?

Question

I have 2 tables, one with price reevaluations and one with minimal redefinitions, each per user and product. How can I select them so that I have an entry for each user and product?

Table 1: Override Prices
+--------+-----------+---------------+
| userID | productID | overridePrice |
+--------+-----------+---------------+
| 4      | 53        | 99.99         |
| 4      | 13        | 99.99         |
| 4      | 55        | 99.99         |
+--------+-----------+---------------+

Table 2: Override Minutes
+--------+-----------+---------------+
| userID | productID | overrideMin   |
+--------+-----------+---------------+
| 4      | 18        | 23            |
| 4      | 55        | 4             |
| 50     | 55        | 2             |
+--------+-----------+---------------+

The table I want to create is:

Table 2: All overrides
+--------+-----------+-------------+---------------+
| userID | productID | overrideMin | overridePrice |
+--------+-----------+-------------+---------------+
| 4      | 13        | null        | 99.99         |
| 4      | 18        | 23          | null          |
| 4      | 53        | null        | 99.99         |
| 4      | 55        | 4           | 99.99         |
| 50     | 55        | 2           | null          |
+--------+-----------+-------------+---------------+

I tried to execute GROUP BY userID, productIDbut since product identifiers may exist in table 1, which are not in table 2, I get different results, depending on which productIDI am group by.

Answer 1

Use UNION:

SELECT userID, productID, 
  MAX(overrideMin) AS overrideMin, 
  MAX(overridePrice) AS overridePrice
FROM
(
    SELECT userID, productID, null AS overrideMin, overridePrice
    FROM OverridePrices
    UNION
    SELECT userID, productID, overrideMin, null AS overridePrice
    FROM OverrideMinutes
) AS t
GROUP BY userID, productID;

This will give you the exact results you are looking for:

| userID | productID | overrideMin | overridePrice |
|--------|-----------|-------------|---------------|
|      4 |        13 |      (null) |         99.99 |
|      4 |        18 |          23 |        (null) |
|      4 |        53 |      (null) |         99.99 |
|      4 |        55 |           4 |         99.99 |
|     50 |        55 |           2 |        (null) |

Answer 2

IF:

SELECT
  t1.userID,
  t1.productID,
  if (t2.overrideMin IS NULL, NULL, t2.overrideMin) AS overrideMin,
  if (t1.overridePrice IS NULL, NULL, t1.overridePrice) AS overridePrice
FROM OverridePrices AS t1
LEFT JOIN OverrideMinutes AS t2 ON t1.userID = t2.userID AND t1.productID = t2.productID;

1 2, , , :

SELECT
  t1.userID,
  t1.productID,
  if (t2.overrideMin IS NULL, NULL, t2.overrideMin) AS overrideMin,
  if (t1.overridePrice IS NULL, NULL, t1.overridePrice) AS overridePrice
FROM (
  SELECT userID, productID FROM OverridePrices
  UNION
  SELECT userID, productID FROM OverrideMinutes
) AS t0
LEFT JOIN OverridePrices AS t1 ON t0.userID = t1.userID AND t0.productID = t1.productID
LEFT JOIN OverrideMinutes AS t2 ON t0.userID = t2.userID AND t0.productID = t2.productID;

, GROUP, HAVING ..

Answer 3

SELECT table1.userID,table1.productID,table2.overrideMin,table1.overridePrice FROM table1 
LEFT JOIN table2 ON table1.userID=table2.userID AND table1.productID = table2.productID 
UNION SELECT table2.userID,table2.productID,table2.overrideMin,table1.overridePrice FROM table1 
RIGHT JOIN table2 ON table1.userID=table2.userID AND table1.productID = table2.productID 
ORDER BY userID, productID

it OUTPUT