Left Join query - 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)

Left Join query

Author

Topic

argaz
Starting Member

8 Posts

Posted - 2006-05-02 : 13:40:42

Is it possible to change this query so it will work?

select * from A LEFT JOIN (B where B.Id=2)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-02 : 13:42:31

select * from A
LEFT OUTER JOIN B
ON A.SomeColumn = B.SomeColumn
where B.Id=2

Tara Kizer
aka tduggan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-05-02 : 14:07:12

or maybe you are after:

select * from A LEFT JOIN (select * from B where B.Id=2) ON A.SomeColumn = B.SomeColumn

??

argaz
Starting Member

8 Posts

Posted - 2006-05-02 : 14:27:04

Thank you, I think that the second is what i wanted but for some reason that doesn't work in my ASP code.

Can you check that query to make sure that it is good?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-05-02 : 14:51:50

Don't write SQL in your ASP code. write it in Query Analzyer. test it, validate it, optimize it, make sure it works, and then attempt to incorporate it into your client code. Or better yet, create a stored procedure.

argaz
Starting Member

8 Posts

Posted - 2006-05-02 : 15:20:06

Ok. I will start doing that. But that query is running when it is:
select * from A LEFT JOIN (B) ON A.SomeColumn = B.SomeColumn

but doesn't run when it is:

select * from A LEFT JOIN (select * from B where B.Id=2) ON A.SomeColumn = B.SomeColumn

so are you sure that it is right?

I think that B.someColumn is a problem because when the -
"select * from B where B.Id=2" was done it can't be called B anymore.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-02 : 15:24:53

You need to alias the derived table:

select * from A LEFT JOIN (select * from B where B.Id=2) B ON A.SomeColumn = B.SomeColumn

Tara Kizer
aka tduggan

argaz
Starting Member

8 Posts

Posted - 2006-05-02 : 15:30:33

That's it!
Thank you.
Great forum.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-05-02 : 17:51:27

Thanks, Tara!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-03 : 02:34:27

argaz,

Also Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources