mysql, count distinct values between two tables

Question

I have two tables

CREATE TABLE TableA  
(ID_A INT,   
P1 INT,
P2 INT,
P3 INT,
P4 INT);

INSERT INTO TableA VALUES
(1,3,4,3,5),
(2,5,4,3,4);

CREATE TABLE TableB
(ID_B INT,
NAME TEXT);

INSERT INTO TableB VALUES
(1,"A"),
(2,"B"),
(3,"C"),
(4,"D"),
(5,"E"),
(6,"F");

Values in Tables

TableA
+------+----+----+----+-----+
| ID_A | P1 | P2 | P3 | P4  |
+------+----+----+----+-----+
|    1 |  3 |  4 |  3 |   5 |
|    2 |  5 |  4 |  3 |   4 |
+------+----+----+----+-----+

TableB
+------+------+
| ID_B | Name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
+------+------+

tableA P1 = TableB ID_B ,

tableA P2 = TableB ID_B ,

tableA P3 = TableB ID_B ,

tableA P4 = TableB ID_B

I want the sum of all the names in TableB that correspond in P1, P2, P3, P4

+----------+------+
| COUNT(*) | Name |
+----------+------+
|        3 | C    |
|        3 | D    |
|        2 | E    |
+----------+------+

Thanks to those who will help me.

Have you try any query? Could you please upload your example query as well? thanks — FanoFN
– FanoFN, Commented Apr 26, 2019 at 8:24

Rima · Accepted Answer · 2019-04-26 10:16:15Z

1

You can handle this using UNION ALL and Derived table.

select count(*) cnt, name 
from 
(
    select b.name 
    from TableB b
    inner join TableA p1 on p1.p1 = b.id_b
    union all
    select b.name 
    from TableB b
    inner join TableA p2 on p2.p2 = b.id_b
    union all
    select b.name 
    from TableB b
    inner join TableA p3 on p3.p3 = b.id_b
    union all
    select b.name 
    from TableB b
    inner join TableA p4 on p4.p4 = b.id_b
)A
group by name
order by name

Output

cnt         name
----------- --------------------------------------------------
3           C
3           D
2           E

https://www.db-fiddle.com/f/nRpqNiUmpRVJJ4gMP66Ywn/9

answered Apr 26, 2019 at 10:16

Rima

1,4551 gold badge9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

carmelocony Over a year ago

Your code looks good I'm verifying, applying in my real query, which is much more complex than the example I wrote in the forum. In TableA I have a Date field and I have to filter by date. As soon as possible, I'll let you know if this code works well for me.

carmelocony Over a year ago

For ch2019 , your code is good. But I realized that I wrote the question wrongly. I accept your answer as correct, and ask another question.

Collectives™ on Stack Overflow

mysql, count distinct values between two tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related