0

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

Wardiam
  • 127
  • 7
  • 1
    Can you try updating inner join to left – svikramjeet Sep 12 '20 at 16:24
  • 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 Sep 12 '20 at 16:38
  • Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Sep 12 '20 at 18:03
  • Please in code questions give a [mre]--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. [ask] Stop trying to code your overall goal & explain what you expected instead from the given code & why. – philipxy Sep 12 '20 at 18:04
  • Can you explain the values in the `duplicate` column? – Mureinik Sep 12 '20 at 19:32
  • Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. PS Please act on all comment content, not just some. PS Please clarify via edits, not comments. – philipxy Sep 12 '20 at 19:34
  • philipxy I have edited the message to try to apply the appropriate formatting and to reduce the message. Logically, when reducing the explanation there may be some things left unclear, but I have tried to clarify everything again. By the way, Left outer join does not solve the problem. – Wardiam Sep 12 '20 at 21:48
  • What is `ean13`? You don't show it in either table. – Schwern Sep 12 '20 at 21:52
  • Sorry Schwern, I made a mistake when writing the query, I have corrected it in the message, I wanted to say d.word1 = c.word2 – Wardiam Sep 12 '20 at 21:56
  • @Wardiam can you explain the column Count? – forpas Sep 12 '20 at 22:03
  • Count = number of times each value in table 2 appears in table 1. @forpas you have this parameter in the sql query ..."count( d.word1 ) AS Count"... – Wardiam Sep 12 '20 at 22:24
  • I don't know why you commented to me. "Please act on all comment content" "cut & paste & runnable code" "code that you show is OK extended by code that you show is not OK" Etc etc. PS One of the errors in your code when I commented re left join was per the link. – philipxy Sep 12 '20 at 22:28

1 Answers1

1

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
forpas
  • 160,666
  • 10
  • 38
  • 76