CODEWITHSUNDEEP
sqljoinimplicit
JTK
JTK

Reputation: 1519

Convert Implicit Join to Explicit

I have a query below that appears to implicitly join 3 tables at once, I'm trying to re-write this to use explicit joins, but I can't get my head around it, it seems like the join column for one join is dependent on a join that hasn't been completed yet.

Is this a scenario that only an implicit join can accommodate?

The issue I'm having is with this peace:

AND((t.object_type = 'SOME_VALUE' and i.pro_prod_id=400 and i.gbcert_id || '-Packing' = ta.gbcert_id)
OR (t.object_type in ('V1','V2','V3') and i.gbcert_unique_id = ta.gbcert_id))

You can see that we are deciding what column to Join on based on whether t.object_type = 'SOME_VALUE' or t.object_type in ('V1','V2','V3')

But I wont have that value yet because there is no Join on t yet, it seem like an egg before the chicken problem to me..

This is a more complete version of the query:

FROM TRANSACTION t, USG_Award ta,
inbox i,
stores s,
clients cl,
client_types ct
WHERE ct.client_type = cl.client_type
AND ct.usg_aggregation_client IS NULL
AND i.orig_store_code = s.sto_store_code
AND i.orig_store_code   = cl.store_code
and ((t.object_type = 'SOME_VALUE' and i.pro_prod_id=400 and i.gbcert_id || '-Pack' = ta.gbcert_id)
OR (t.object_type in ('V1','V1','V1') and i.gbcert_unique_id = ta.gbcert_id))
AND ta.fk_usg_tx = t.pk_usg_tx

This query works in it's current form, it's legacy code that I did not write. The code has been sanitized.

Upvotes: 2

Views: 555

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can move such conditions into an ON clause:

FROM TRANSACTION t JOIN
     USG_Award ta
     ON ta.fk_usg_tx = t.pk_usg_tx JOIN
     inbox i 
     ON ((t.object_type = 'SOME_VALUE' and i.pro_prod_id = 400 and i.gbcert_id || '-Pack' = ta.gbcert_id) OR
         (t.object_type in ('V1', 'V1', 'V1') and i.gbcert_unique_id = ta.gbcert_id)
        )JOIN
     stores s
     ON i.orig_store_code = s.sto_store_code JOIN
     clients cl 
     ON i.orig_store_code = cl.store_codeJOIN
     client_types ct
     ON ct.client_type = cl.client_type
WHERE ct.usg_aggregation_client IS NULL

Upvotes: 2

Related Questions