INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Left Outer Join Syntax error

Left Outer Join Syntax error

(OP)
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.

RE: Left Outer Join Syntax error

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

RE: Left Outer Join Syntax error

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):

CODE

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.

RE: Left Outer Join Syntax error

(OP)
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'.

RE: Left Outer Join Syntax error

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.

RE: Left Outer Join Syntax error

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 ppd ON tr.loc_n=ppd.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.

RE: Left Outer Join Syntax error

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.

RE: Left Outer Join Syntax error

(OP)
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"

RE: Left Outer Join Syntax error

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close