Left joining with where-joining

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

Transact-SQL (2000)

Left joining with where-joining

Author

Topic

Jacob
Starting Member

2 Posts

Posted - 2002-10-04 : 06:30:58

If I have three simple tables like this

tblname
nameid
name

tblnametogroup
nameid
groupid

tblgroup
groupid
group

select name, group from (tblname left join tblnametogroup on tblname.nameid = tblnametogroup.nameid) left join tblgroup on tblnametogroup.groupid = tblgroup.groupid

will give me a list of all names including names not in any group

select name, group from tblname, tblnametogroup, tblgroup where tblname.nameid=tblnametogroup.nameid and tblnametogroup.groupid=tblgroup.groupid

will only give me names that are in a group.

I am wondering if it is possible to get a list with all names including them not in a group by using where-joining ?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-04 : 06:41:40

You mean you just want the name that are not in a group?

select name, group
from tblname
left join tblnametogroup
on tblname.nameid = tblnametogroup.nameid
left join tblgroup
on tblnametogroup.groupid = tblgroup.groupid
where tblgroup.groupid is null

or easier (assuming your data is correct)
from tblname
left join tblnametogroup
on tblname.nameid = tblnametogroup.nameid
where tblnametogroup.groupid is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jacob
Starting Member

2 Posts

Posted - 2002-10-04 : 07:26:06

No, I am curious if it is possible to get a list like

name - group
Jacob - ASP
nr - SQL
Homer - NULL

This is easy using left join.

But is it possible in a select where the joins are specified in the where-section ?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-10-04 : 07:29:12

you need to look at using a NOT EXISTS subquery...

ie....select * from customer where not exists (select * from accounts where accounts.fkcustid = customer.id)

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-10-04 : 08:05:44

Jacob,

I think you want the syntax you want is as follows:

select name, group
from tblname, tblnametogroup, tblgroup
where tblname.nameid *=tblnametogroup.nameid
and tblnametogroup.groupid *=tblgroup.groupid

I have found your first join (tblname left join tblnametogroup on tblname.nameid = tblnametogroup.nameid left join tblgroup on tblnametogroup.groupid = tblgroup.groupid) to be much more efficient. You may also run into problems if you use the above syntax in nested subqueries.

Jeremy

Edited by - joldham on 10/04/2002 08:06:53

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-04 : 08:14:51

Jacob, you should NOT use the =* construct. Use the ANSI Standard LEFT JOIN in your from clause. The =* will not be supported in the next version of SQL, and I believe is buggy in SQL 2K.

Jay White
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-04 : 09:13:20

select name, group
from tblname, tblnametogroup, tblgroup
where tblname.nameid *=tblnametogroup.nameid
and tblnametogroup.groupid *=tblgroup.groupid

Can't do this only one level of outer join is allowed with the abbreviated syntax - it's one reason for using left outer join.

select name, group
from tblname , (select nameid, group from tblnametogroup , tblgroup where tblnametogroup.groupid = tblgroup.groupid) as a
where tblname.nameid *= a.nameid

might work
--(edit) nope the server is too smart for me.

otherwise a union to return the ones with groups and the ones without.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 10/04/2002 09:29:08

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-10-04 : 10:47:29

I have to second what nr and page47 have said. I came from an Oracle background and used this notation until I began running into problems. Since nr mentioned it, I remember one of my biggest problems was not being able to accomplish two left joins. You really should use the ANSI Standard LEFT JOIN in your from clause.

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources