view records not in the JOIN? [RESOLVED]

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)

view records not in the JOIN? [RESOLVED]

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-02 : 14:03:34

Is there a way to view what records this is not pulling:


SELECT p.casenumber FROM tblParadox as p INNER JOIN tblParadoxOther as po
ON p.CaseNumber = po.CaseNumber AND p.PartNumber = po.PartNumber

It is pulling 19121 records, but there are 19134 records in both tables.

I need to see why it is not pulling those so I can fix it. Thanks!

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-02 : 14:11:26

LEFT OUTER JOIN instead of INNER JOIN. Take a look at the differences between the two types of JOINs in BOL.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-02 : 14:19:03

When you have fixed the records, make sure that the columns do not allow null, and set up a FK relationship between the tables.
That will ensure that the situation doesn't arise againn.

rockmoose

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-02 : 14:19:26

Thanks again Tara!

Brenda

If it weren't for you guys, where would I be?

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-02 : 14:30:20

There are a few duplicate records in the table(the data I got from our old database). How can I test to see which ones they are?

select * from tblParadox
where casenumber = casenumber????

I don't have any PK and FK set on those tables yet. Our data is too screwed to do that yet.

Brenda

If it weren't for you guys, where would I be?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-02 : 14:40:09

select tblParadox.* from tblParadox
join
( select casenumber from tblParadox
group by casenumber having count(*) > 1 ) as dups
on tblParadox.casenumber = dups.casenumber

rockmoose

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-02 : 14:56:55

Thanks rockmoose.

I had to do this though:

select tblParadox.* from tblParadox
join
(select casenumber,partnumber from tblParadox
group by casenumber, partnumber
having count(*) > 1 ) as dups
on tblParadox.casenumber = dups.casenumber and tblParadox.partnumber = dups.partnumber

Brenda

If it weren't for you guys, where would I be?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-02 : 15:01:13

Ahh, yes I didn't read the first post very carefully.
Have fun cleaning up the data

rockmoose

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-02 : 15:15:46

Yeah, not so fun on cleaning up 272 records :(

Thanks again!

Brenda

If it weren't for you guys, where would I be?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-05 : 08:40:03

Would it make sense to use a FULL OUTER JOIN in lieu of the LEFT OUTER JOIN?

HTH

=========================================
Let X = {All sets s such that s is not an element of s}

(X element of X) ==> (X not element of X)
(X not element of X) ==> (X element of X)

(Bertrand Russell Paradox)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-05 : 08:59:26

> Would it make sense to use a FULL OUTER JOIN in lieu of the LEFT OUTER JOIN?

Yes if you want to find the records in A but not in B and also the records in B but not in A.
A not in B + B not in A
select A.key, b.key from A full join B on A.key = B.key where A.key is null or b.key is null

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources