Problems to join 2 queries

Question

I have a MySQL database with two tables:

Table 1 (table1_Temp), is a temporary table that has only one column (word1) with these values:

+-------+
| word1 |
+-------+
| aaa   |
| aaa   |
| bbb   |
| ccc   |
| ccc   |
| ccc   |
| ddd   |
+-------+

Table 2 (table2_stock) has 3 columns with these values:

+-----+-------+-------+
| id  | word2 | stock |
+-----+-------+-------+
| 001 | aaa   |     2 |
| 002 | ccc   |     5 |
| 003 | ccc   |     3 |
| 004 | ddd   |     1 |
| ... | ...   |   ... |
+-----+-------+-------+

I have tried with this code::

SELECT
  c.id,
  c.word2,
  c.stock,
  count( d.word1 ) AS Count
FROM
  table2_stock c
INNER JOIN table1_Temp d ON d.word1 = c.word2
GROUP BY c.id

But the result is:

+-----+-------+-------+-------+
| id  | word2 | stock | Count |
+-----+-------+-------+-------+
| 001 | aaa   |     2 |     2 |
| 002 | ccc   |     5 |     3 |
| 003 | ccc   |     3 |     3 |
| 004 | ddd   |     1 |     1 |
+-----+-------+-------+-------+

Count = number of times each value in table 2 appears in table 1.

I would like to get the unique values from table 1, to know which values are duplicated in table 2 and cross the information with table 2 but leaving all the values including those not present in table 2 (e.g. bbb).

This would be my desired result:

+------+-------+-------+--------+-----------+
|  id  | word2 | stock | Count  |*Duplicate*|
+------+-------+-------+--------+-----------+
| 001  | aaa   | 2     | 2      |         1 |
| NULL | bbb   | NULL  | 1      |         0 |
| 002  | ccc   | 5     | 3      |         2 |
| 003  | ccc   | 3     | 3      |         2 |
| 004  | ddd   | 1     | 1      |         1 |
+------+-------+-------+--------+-----------+

Duplicates = number of times each unique value in table 1 appears in table 2.

In this way you would have all the information on both stock and the number of duplicates in a single table.

How could I get to combine both tables and know what the duplicate values are?.

Thank you very much.
Wardiam

If I use Left Join the value bbb does not appear. But there is another problem and that is that I simplified my example because table2 contains many more values than the ones shown in the example and I only want to retrieve the data from the values shown in table1 — Wardiam
Does this answer your question? Left Outer Join doesn't return all rows from my left table? — philipxy
Please in code questions give a minimal reproducible example--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. How to Ask Stop trying to code your overall goal & explain what you expected instead from the given code & why. — philipxy

forpas forpas · Accepted Answer · 2020-09-12T22:39:07

With a LEFT Join of table1_Temp to table2_stock and aggregation:

select t2.id, t1.word1, t2.stock,
       count(t1.word1) Count,
       (select count(*) from table2_stock where word2 = t2.word2) Duplicate
from table1_Temp t1 left join table2_stock t2
on t2.word2 = t1.word1
group by t2.id, t1.word1, t2.stock
order by t1.word1

See the demo.
Results:

>   id | word1 | stock | Count | Duplicate
> ---: | :---- | ----: | ----: | --------:
>    1 | aaa   |     2 |     2 |         1
> null | bbb   |  null |     1 |         0
>    2 | ccc   |     5 |     3 |         2
>    3 | ccc   |     3 |     3 |         2
>    4 | ddd   |     1 |     1 |         1

Problems to join 2 queries

1 Answers