CODEWITHSUNDEEP
sqldatabaseoracle-databaseouter-join
jayjaypg22
jayjaypg22

Reputation: 1751

Multiple outer join in the same request

I want to do a multiple outer join on a table with 3 others. The join is niddeclaration. The goal is to obtain a declaration with everything in relationship with it in my schema. The existing code did one outer join with the old syntax : where A.x= B.x (+). The 'new ' syntax left outer join seems to do exactly the same. My problem is to use it multiple times in the same request. It seems that I can't use a unique 'declaration d' identifier, that's why I use 3 differents to do the same thing instead of one.

select * 
  FROM entreprise e, 
       calendrier m, 
       statut s, 
       lignedeclaration l, 
       categoriedeclaration c, 
       declaration d left OUTER JOIN saisiecategoriedeclaration sc on d.niddeclaration = sc.niddeclaration ,
       declaration de left OUTER JOIN saisielignedeclaration sl on de.niddeclaration = sl.niddeclaration,
       declaration dl left OUTER JOIN reglement r on dl.niddeclaration = r.niddeclaration
 WHERE (d.nidentreprise = e.nidentreprise) 
   AND d.niddeclaration = 12314689 
   and de.niddeclaration = 12314689 
   and dl.niddeclaration = 12314689;

In brief I want to do 2 normal join and 3 outer join in the same request. With the 3 outer join with the same 'left' table.

Upvotes: 1

Views: 3132

Answers (1)

Simon
Simon

Reputation: 1207

You don't need to re-include declaration.

declaration d
left OUTER JOIN saisiecategoriedeclaration sc on d.niddeclaration = sc.niddeclaration
left OUTER JOIN saisielignedeclaration sl on d.niddeclaration = sl.niddeclaration
left OUTER JOIN reglement r on d.niddeclaration = r.niddeclaration

Note the omission of the commas. You may also need to re-write the other tables in the query to use the modern join syntax, I'm not sure what happens if you try to mix the syntaxes.

Upvotes: 4

Related Questions