Right and Left Join Together in SQL

Question

I am trying to use a RIGHT and LEFT Join together. I have it working when only left joining one table. But I am trying to now include another table in my left join. It gives me an error saying I am missing an operator. Where am I missing a parenthesis?

FROM qSplit RIGHT JOIN (t_i360_agent AS i LEFT JOIN cmsAgent_Split AS c 
ON ((i.LocalDay = c.LocalDay) AND (i.ACDID = c.LOGID)) 
LEFT JOIN qry_AllNewtables as qry ON (qry.custConvDate = c.LocalDay)
AND (qry.CustAgentLoginName = i.Loginname)  ) ON qSplit.SPLIT = c.SPLIT

Give your DBMS for SQL questions--as you were explicitly told to do when tagging yours. Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS This code is invalid so how are we supposed to know what you want it to do? When describing a result: Say enough that someone could go away & come back with a solution. — philipxy, May 20 '19 at 23:57
Please format code (including indentation) to reflect semantics (including parentheses) as you should in any language. — philipxy, May 21 '19 at 00:50

score 1 · Accepted Answer · answered May 20 '19 at 17:49

Don't use LEFT JOIN and RIGHT JOIN together. I imagine that somewhere there could be a query where it makes sense. In practice, I don't think I have ever used them both in the same query, possibly because I write queries using LEFT JOIN.

If you want everything in the agent table, then make it first! And use left join;

FROM t_i360_agent i LEFT JOIN
     cmsAgent_Split c 
     ON i.LocalDay = c.LocalDay AND i.ACDID = c.LOGID LEFT JOIN 
     qry_AllNewtables qry
     ON qry.custConvDate = c.LocalDay AND 
        qry.CustAgentLoginName = i.Loginname LEFT JOIN
     qSplit
     ON qSplit.SPLIT = c.SPLIT

It is much easier to follow the intention of the query this way. You are starting with the data that you think is so important that you want to keep all of it, even when JOINs have no matching rows.

score 0 · Answer 2 · answered May 20 '19 at 17:52

0

Beside the suggestion by Gordon the problem was you miss a join condition for the RIGHT JOIN once I reformat the query was easy to see what was missing.

FROM qSplit 
RIGHT JOIN t_i360_agent AS i 
  -- Need join condition
LEFT JOIN cmsAgent_Split AS c 
  ON (i.LocalDay = c.LocalDay) 
 AND (i.ACDID = c.LOGID)) 

LEFT JOIN qry_AllNewtables as qry 
  ON (qry.custConvDate = c.LocalDay)
 AND (qry.CustAgentLoginName = i.Loginname)

answered May 20 '19 at 17:52

Juan Carlos Oropeza

47,252
12
78
118

Where is the analogue of qSplit.SPLIT = c.SPLIT? PS Or the parentheses are correct & errors are elsewhere. – philipxy May 21 '19 at 00:50

Right and Left Join Together in SQL

2 Answers2