Need Help in SQL 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)

Need Help in SQL Query!

Author

Topic

claire99
Starting Member

17 Posts

Posted - 2002-11-06 : 11:55:32

I have two tabels (A and B) . And both tables have two fields called No. and Date. I want get the result that in A that hasn't the same No. and Date at the same time in B. For example,

A:
No. Date
111 1/1/2002
222 1/1/2002
222 2/2/2002
333 1/1/2002
333 2/2/2002
333 3/3/2002
444 4/4/2002

B:
NO. Date
222 2/2/2002
333 3/3/2002

The result should be
111 1/1/2002
222 1/1/2002
333 1/1/2002
333 2/2/2002
444 4/4/2002

How do I write a SQL query to get this result?
Thanks.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-06 : 11:58:53

SELECT A.No,A.Date FROM A LEFT OUTER JOIN B ON A.No = B.No AND A.Date = B.Date WHERE B.No IS NOT NULL

Sarah Berger MCSD

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-06 : 12:18:59


select
    a.no,
    a.date
from
    a
where
    not exists(
        select 1
        from
            b
        where
            a.no = no and
            a.date = date)

Jay White
{0}

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-06 : 12:26:06

I have seen somewhere on a SQLTeam post (by NR, I think) that using an outer join approach and eliminating nulls is faster than using EXISTS. That post dealt with deleting non-matching rows.

Sarah Berger MCSD

claire99
Starting Member

17 Posts

Posted - 2002-11-06 : 12:39:23

Thanks, all!
I used Sarah's query. It works when I use "B.NO IS NULL" instead of "B.NO IS NOT NULL".

Claire

claire99
Starting Member

17 Posts

Posted - 2002-11-14 : 09:30:30

Good!

"And we know that all things work together for good to them that love God, to them who are the called according to his purpose." --- Roman 8:28

claire99
Starting Member

17 Posts

Posted - 2002-11-14 : 09:32:33

test

-------------------
"And we know that all things work together for good to them that love God, to them who are the called according to his purpose." --- Roman 8:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources