I want to find in
Dx1, Dx2, Dx3
ID Dx 1 Dx 2 Dx 3
1 1 1 1
2 1 1 2
I am giving the answer although your question seems implicit.
SELECT Categories.Name, COUNT(*) AS Count_dx FROM Categories INNER JOIN ( SELECT Dx1 dx_val FROM Dx UNION ALL SELECT Dx2 dx_val FROM Dx UNION ALL SELECT Dx3 dx_val FROM Dx ) AS t ON Categories.ID = t.dx_val GROUP BY t.dx_val;
Probably you want to get the count of category IDs (for each category) across the three columns (
Dx1, Dx2 and Dx3) in
Dx table. If so then the above query does the job.