Need to find records that match on one column but not on another

Question

Post reply

Need to find records that match on one column but not on another

Ron-279530

Ten Centuries

Points: 1381
More actions
October 4, 2007 at 11:48 am

#63093

I've searched here and a few other places but can't find syntax that will help me find find records that match on one column but not on another.
This is my most recent effort:
Select *
From History
Where DialID = DialID
and exists (Select *
From History
Where ProjectID <> ProjectID)
I've also tried something simpler such as this:
SELECT top 100 *
FROM History
WHERE DialID = DialID
AND ProjectID <> ProjectID

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Select *
From History
Where ProjectID <> ProjectID

I'm assuming one of these "ProjectID" fields resides in a different table?????

The above statement will never return a record because ProjectId will always ProjectID.

What is the criteria of what the statement should exclude?

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ron-279530 Ten Centuries Points: 1381 More actions · Answer 2

The data is all in the same table and what I'm looking to return are all records from one table where the ProjectID is different and the DialID is the same.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

So are there only duplicates in the table or can there be triplicates, quad's etc...?

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

Select t1.*

From History t1

join history t2

on t1.DialID = t2.DialID and

t1.ProjectID <> t2.ProjectID

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

CREATE TABLE #test (dialId INT, projectId INT)

INSERT #test

SELECT 1,1 UNION ALL

SELECT 2,5 UNION ALL

SELECT 2,6 UNION ALL

SELECT 2,7 UNION ALL

SELECT 3,2 UNION ALL

SELECT 3,2

SELECT

t1.*

FROM

#test t1

INNER JOIN (SELECT t2.dialId, COUNT(DISTINCT t2.projectId) AS idCount FROM #test t2 GROUP BY dialID) t3

ON t1.dialID = t3.dialID

AND t3.idCount > 1

DROP TABLE #test

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 6

mrpolecat (10/4/2007)
Select t1.*
From History t1
join history t2
on t1.DialID = t2.DialID and
t1.ProjectID <> t2.ProjectID

Close, but results in a cartesian product.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

mrpolecat SSCertifiable Points: 7014 More actions · Answer 7

I expected there to be a PK and then would have selected distinct PK to get the actual rows. I guess with a large table your way would be the better performer.

John Rowan SSC Guru Points: 56440 More actions · Answer 8

Ron, got you message. I would use what Jason has suggested here. Give me a call if you need to discuss further. BTW, I got your e-mail and I am not in a place to look at your server. Give me a call if you need to; otherwise, I can look into this tonight or tomorrow as I will be working from my home office.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ron-279530 Ten Centuries Points: 1381 More actions · Answer 9

Thanks to all, this helped me get something usable.

John, I'll cc you on an email regarding this issue and will call do discuss.

Thanks again!