asked    Kyle     2018-10-22       sql       110 view        3 Answers

[SOLVED] How to rewrite implicit joins to explicit joins?

I'm having some trouble re-writing these JOINS using explicit joins. I'm trying to understand how this is accomplished. Here is the original query using implicit joins that I've inherited:

FROM prod.f31122 t,
       prod.f4801   wo,
       prod.f30008  r,
       prod.f0101   ab
WHERE t.wtdoco = wo.wadoco(+)
   and t.wtmmcu = wo.wammcu(+)
   and t.wtmmcu = r.iwmcu
   and t.wtan8 = ab.aban8

I've tried the following, but I'm getting different result sets. The same count, but the result set is incorrect (likely an issue with my groupings which aren't displayed). Here is my translation using explicit joins:

FROM prod.f31122 t      
JOIN prod.f4801 wo ON t.wtdoco = wo.wadoco
JOIN prod.f4801 wo ON wo.wammcu = t.wtmmcu 
JOIN prod.f30008 r ON t.wtmmcu = r.iwmcu 
JOIN prod.f0101 ab ON t.wtan8 = ab.aban8 

I just want to make sure that my translation from implicit joins to explicit joins is logically correct (for my education and understanding). For the record, I can keep the implicit joins, but I am just trying to understand this process better.

  3 Answers  

        answered    Sam     2018-10-22      

FROM prod.f31122 t      
LEFT JOIN prod.f4801 wo ON t.wtdoco = wo.wadoco AND wo.wammcu = t.wtmmcu 
JOIN prod.f30008 r ON t.wtmmcu = r.iwmcu 
JOIN prod.f0101 ab ON t.wtan8 = ab.aban8 

You join to wo only once, and the two join conditions appear in the same ON clause, with the logical operator AND between them.

LEFT JOIN can also be written as LEFT OUTER JOIN, and JOIN can be written as INNER JOIN. I prefer these longer phrases since they are more explicit.



        answered    Perry     2018-10-22      

This is not quite correct.

  1. Multiple join criteria are implemented with one instance of the table and AND conditions
  2. The (+) is the old Oracle syntax for outer joins. JOIN on its own defaults to INNER JOIN so you need to correct that.

So it should look like this:

FROM prod.f31122 t      
LEFT OUTER JOIN prod.f4801 wo ON t.wtdoco = wo.wadoco
                              AND t.wtmmcu = wo.wammcu
JOIN prod.f30008 r ON t.wtmmcu = r.iwmcu 
JOIN prod.f0101 ab ON t.wtan8 = ab.aban8 


        answered    Barret     2018-10-22      

This part is wrong:

FROM prod.f31122 t      
JOIN prod.f4801 wo ON t.wtdoco = wo.wadoco
JOIN prod.f4801 wo ON wo.wammcu = t.wtmmcu 

You use AND to do two things on a single join, not two separate JOINs. For example:

FROM prod.f31122 t      
LEFT JOIN prod.f4801 wo
    ON t.wtdoco = wo.wadoco and wo.wammcu = t.wtmmcu 

Using "JOIN" twice would literally join the same table two separate times, so you would have two copies of all the fields based on the different join conditions.

Corrections

Per @MathGuy & @AaronDietz comments I switched this to a LEFT JOIN due to the (+) presence in the question.





Your Answer





 2018-10-22         Regan

Jquery validation error message in checkbox validation

This question already has an answer here: Placing error message for a checkbox array 2 answers jQuery Validation plugin error message placement for radio button 2 answers I found a way to work around it. My checkbox name is 'single_service'. firstly i hide the label error message that created by validation plugin with simple css.label#single_service-error {display: none !important; }then i added invalidHandler...
 jquery                     1 answers                     44 view
 2018-10-22         Hiram

Add fields based on number (Jquery)

I am trying to add X fields according to the number type in the number field or so when the value increases.My template:<div class="form-group row"> <label for="inputEmail3" class="col-sm-10 col-form-label">{l s='Nombre de voyageurs' mod='blocksouhaits'}</label> <div class="col-sm-2"> <input type="number" class="form-control" id="nbVoyager"> </div></div><div class="form-group row"> <div class="col-sm-6"> <input type="text" class="form-control" id="lastnameVoyager" placeholder="Nom"> </div> <div...
 jquery                     1 answers                     46 view
 2018-10-22         Julia

Close Animation On Body Click

How can I close an animation when i press on body , not the element that make the animation appear/dissapear?$('#btt').click(function() { $('.box').slideToggle('slow');});.box { width: 200px; height: 300px; background-color: #00000025; display: none;}<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script><button id="btt">PressIt</button><div class="box"></div>You need to do two things:Use slideUp() when the body is clickedStop propagating the click event when you click .box element so that the body cl...
 jquery                     1 answers                     44 view