Finding records with no match? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Finding records with no match?

Author

Topic

de9625
Starting Member

17 Posts

Posted - 2002-05-03 : 04:49:31

I have to tables, one with memebers and one with activities. How do I find all the members that don't have any activities? I basically want to find the records that dont have a match in the other table.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-05-03 : 04:53:26

use not in

select * from members where id not in(select id from activities)

======================================
Ask to your self before u ask someone

de9625
Starting Member

17 Posts

Posted - 2002-05-03 : 05:01:45

ops...Hmm I must have started out from the wrong direction! It seemed much more difficult when I started out ;-)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-03 : 15:44:30

Three ways to do the same thing . . .


use pubs

-- a la khalik
select
    au_id
from
    authors
where
    au_id not in (
        select
            au_id
        from
            titleauthor)

-- left join where the right side is null
select
    a.au_id
from
    authors a
    left join titleauthor ta
        on a.au_id = ta.au_id
where
    ta.au_id is null

-- basically same as khalik
select
    a.au_id
from
    authors a
where
    not exists (
    select
        1
    from
        titleauthor
    where
        a.au_id = au_id)

Based on indexing, one may give better performance than another . . .

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources