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.
Desiured Query Result:
|ID||# OF TIMES THAT taxcode = earncode||# OF TIMES THAT dductcode = earncode||# OF TIMES THAT taxcode = dductcode|
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)