Left Outer Join Syntax error

[capronton]

Can someone explain why the query below is getting the syntax error below?
How could I re-write this query to do a LEFT OUTER JOIN on the fields/columns below?

[SQL]: select ts,tr.type,et.text,tr.n,tr.loc_n,bus,drv,run,route,ttp,grp,des,seq,tpbc,longitude,latitude,tr.fs,amt


FROM tr LEFT OUTER JOIN trd ON tr.loc_n=trd.loc_n

LEFT OUTER JOIN tr ON tr.loc_n=ppd.loc_n
LEFT OUTER JOIN tr ON tr.id=ppd.id
LEFT OUTER JOIN tr ON tr.tr_seq=ppd.tr_seq
LEFT OUTER JOIN tr ON tr.loc_n=trmis.loc_n
LEFT OUTER JOIN tr ON tr.id=trmis.id
LEFT OUTER JOIN tr ON tr.tr_seq=trmis.tr_seq
LEFT OUTER JOIN tr ON tr.type=et.type

where ts >= '2016-09-19 04:00:00.000' and ts <= '2016-09-21 03:59:00.000'

and drv in (17410)

order by ts
[Error]: The objects "tr" and "tr" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

 

[capronton]

Basically, I'm trying to pull all records in the "tr" table even though they have no corresponding row in the other tables.

 

[Olaf Doschke]

You just join any table once only. And if you want to join a table multiple times, each further joined instance of the table has to have another name.

If you have many join conditions, you still only do one join, but combine the conditions with boolean algebra, AND and/or OR, typically AND (all conditions must be fulfilled):

Select * from tablea LEFT JOIN tableb ON tablea.f1 = tableb.f1 AND tableaf2=tableb.f2....

This will get all rows of tablea, not matter if there is any row in table b matching the join conditions. If there is more than one row in tableb for tablea, that's fine, they will create more than one result row in that case, but you will find all tablea data in the result.

Bye, Olaf.

 

[capronton]

Thanks for your help.
The LEFT OUTER JOIN is working, however the statement "LEFT JOIN tr ON et tr.type = et.type"
is giving me the error below.

How would I LEFT OUTER JOIN table "tr.type" to "et.type"?


[SQL]: select ts,tr.type,tr.loc_n,bus,drv,run,route,ttp,grp,des,seq,tpbc,longitude,latitude,tr.fs

from tr LEFT JOIN ppd ON tr.loc_n = ppd.loc_n
AND tr.id=ppd.id
AND tr.tr_seq = ppd.tr_seq
LEFT JOIN tr ON et tr.type = et.type

where ts >= '2016-09-19 04:00:00.000' and ts <= '2016-09-21 03:59:00.000'
and drv in (17410)

order by ts
[Error]: An expression of non-boolean type specified in a context where a condition is expected, near 'tr'.

 

[Olaf Doschke]

As said, you have to comine all join conditions into one. the LEFT JOIN keywords only appear once per join, you join two tables with one join. If the join condition is about multiple fields, you don't write multiple JOINS, you continue the join condition with t1.f1=t2.f1 AND t1.f2=t2.F2 AND ... No further LEFT JOINS. This is all just one join condition comparing multiple fields.

Bye, Olaf.

 

[Olaf Doschke]

There's more to it, as your right hand side expressions are about yet other tables, for example LEFT OUTER JOIN tr ON tr.loc_n=ppd.loc_n addresses a table ppd, but you never join that.

LEFT OUTER JOIN tr ON tr.loc_n=ppd.loc_n should be LEFT OUTER JOIN [highlight #FCE94F]ppd[/highlight] ON tr.loc_n=[highlight #FCE94F]ppd[/highlight].loc_n

Then next lines still are about the pair of tables tr and ppd: tr.loc_n=ppd.loc_n AND tr.id=ppd.id AND tr.tr_seq=ppd.tr_seq. These three conditions are ALL about one join of tr and ppd.

You have lots of such wrong joins here, partly you have to change name of joined table, partly you have to simply AND the next condition.

Bye, Olaf.

 

[Olaf Doschke]

Did you define foreign key constraints in your database? Because if so, you could use the visual query designer of SSMS and it would know join conditions and create the essential sql query for you, when you just add tables you want to join. With the query at hand you could then extend and modify it to your needs.

Bye, Olaf.

 

[capronton]

Since I was trying to join "tr" to another table other than "ppd" (i.e., table "et") than I had to
change the statement below to a "RIGHT OUTER JOIN" and this seems to be working okay. See the working statement below.

Thanks for all your help with this.

"RIGHT JOIN et ON et.type = tr.type

 

[Olaf Doschke]

Well, LEFT or RIGHT depends on which of the involved tables is child and parent table of a relation and thus of the join. But judging from your field names you try to join tr with trd, ppd, trmis and et, so you'd need 4 JOINS, but no more, and maybe you really want to join some tables multiple times, but then would need alias names (AS somename) to make a distinction about them. So I think you'll need to mend a lot more than that. You typically can have all LEFT joins, if tables are in opposite order in your series of joins, but that's not necessarily better.

Bye, Olaf.