Select only matching results from 3 separate tables in mysql-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

You can use a "case" statement to produce a 1 if the 2 columns are equal or 0 otherwise. Then take the sum.

SELECT 
t1.id,
SUM(CASE WHEN taxcode = earncode THEN 1 ELSE 0 END) AS NUM_TIMES_TAXCODE_EARNCODE,
SUM(CASE WHEN dductcode = earncode THEN 1 ELSE 0 END) AS NUM_TIMES_DDUCTCODE_EARNCODE,
SUM(CASE WHEN taxcode = dductcode THEN 1 ELSE 0 END) AS NUM_TIMES_TAXCODE_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
GROUP BY 
t1.id