[SOLVED] Select only matching results from 3 separate tables in mysql

Issue

I want to return the results where at least 1 code from each of the two tables exists.

I know that I can do:

SELECT t1.id, taxcode, earncode, dductcode
FROM table_1 t1
JOIN table_2 t2 ON t1.id = t3.id
JOIN table_3 t3 ON t1.id = t3.id
WHERE taxcode = earncode OR taxcode = dductcode OR dductcode = earncode

but, for example, this will show all of the dductcodes for 1 matching pair of taxcodes = earncodes. Instead I would rather return the number of times each match appears for an ID.

table_1

id taxcode
1 ABC
1 DEF
1 GHI
2 CAT
2 JOL

table_2

id earncode
1 ABC
1 NGM
1 GHI
2 CAT
2 YPL

table_3

id dductcode
1 ABC
1 QST
1 RBD
2 CAT
2 YPL

Desiured Query Result:

ID # OF TIMES THAT taxcode = earncode # OF TIMES THAT dductcode = earncode # OF TIMES THAT taxcode = dductcode
1 2 1 1
2 1 2 1

Solution

You can use CTE’s to avoid the one-to-many trap; however, I feel it’s odd that we’re pretending that id’s are found in all 3 tables. This works as long as that stays true. If not, then you may need some left joins and tweak this a bit.

    with tax_equals_earn as 
    (SELECT t1.id, 
    sum(case when t1.taxcode = t2.earncode then 1 else 0 end) as ttotal
    FROM table_1 t1
    JOIN table_2 t2 ON t1.id = t2.id
    group by t1.id
    ),
    tax_equals_dduct as 
    (SELECT t1.id, 
    sum(case when t1.taxcode = t3.dductcode then 1 else 0 end) as ttotal
    FROM table_1 t1
    JOIN table_3 t3 ON t1.id = t3.id
    group by t1.id
    ), 
    earn_equals_dduct as
    (
    select t2.id, 
    sum(case when t2.earncode = t3.dductcode then 1 else 0 end) as ttotal
    from table_2 t2
    join table_3 t3 on t2.id = t3.id
    group by t2.id
    )
    select distinct t1.id, 
    tee.ttotal as tax_equals_earn_times, 
    ted.ttotal as tax_equals_dduct_times, 
    eed.ttotal as earn_equals_dduct_times
    from table_1 t1
    join tax_equals_earn tee on t1.id = tee.id
    join tax_equals_dduct ted on t1.id = ted.id
    join earn_equals_dduct eed on t1.id = eed.id

Db-fiddle found here.

Answered By – Isolated

Answer Checked By – Terry (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.