Adding an outer join? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Adding an outer join?

Author

Topic

lewiska1
Starting Member

12 Posts

Posted - 2005-09-05 : 18:25:10

I have the following query which works fine.


SELECT TOP 5 *,S_Name 
FROM S_Status,SS 
WHERE S = 1
AND SS.ID = S_Status.S

I want to add a LEFT Join like this but get a syntax error


SELECT TOP 5 *,S_Name 
FROM S_Status,SS 
LEFT JOIN IR ON S_Status.ID = IR.S_Status
WHERE S = 1
AND SS.ID = S_Status.S

however the following works


SELECT TOP 5 *
FROM S_Status
LEFT JOIN IR ON S_Status.ID = IR.S_Status
WHERE S_Status.S = 1

Where am I going wrong? [edit] I am using MS Access [/edit]

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-05 : 22:16:45

i believe instaed of the comma, it should be a space?

quote:

FROM S_Status,SS

or if SS is another table, you need to identify which field they are going to be joined together like the one with IR and S_Status

HTH

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-06 : 01:18:07

How many table are trying to join from this?

quote:
SELECT TOP 5 *,S_Name
FROM S_Status,SS
LEFT JOIN IR ON S_Status.ID = IR.S_Status
WHERE S = 1
AND SS.ID = S_Status.S

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-06 : 02:07:26

You are joining IR to S_Status, so you have to place it after S_Stauts, NOT after SS


SELECT TOP 5 *,S_Name 
FROM S_Status
    LEFT JOIN IR 
        ON S_Status.ID = IR.S_Status
    ,SS 
WHERE     S = 1
      AND SS.ID = S_Status.S

However, you would be better off to use the newer style JOIN syntax for the other table too:


SELECT TOP 5 *,S_Name 
FROM S_Status
    LEFT JOIN IR 
         ON S_Status.ID = IR.S_Status
    JOIN SS 
        ON SS.ID = S_Status.S 
WHERE S = 1

Kristen

lewiska1
Starting Member

12 Posts

Posted - 2005-09-06 : 10:39:07

Much thanks for your replies guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources