Query Repeating Data

[696796]

Hi,

I am looking to be able to pick out all of my records which have one part of their primary key the same.

The lowdown:-

So my tables records have a primary key of issueID and IssueDate. IssueId can be the same, but issueDate is unique making it a valid primary key.

Now how do i find all the instances in my db, where records with the same primary key (issue id) occur? Is there some simple syntax i can use in the query builder to find these repeat occurances?

Thanks for your time + effort,

kind regards,

Alex

 

[gojohnnygogogogo]

select distinct issueid from table where issueid in (select distinct issueid from table)

try that.

 

[ddean1968]

SELECT DISTINCTROW TABLENAME.ISSUEDATE, TABLENAME.ISSUEID
FROM TABLENAME
WHERE (((TABLENAME.ISSUEDATE) In (SELECT [ISSUEDATE] FROM [TABLENAME] As Tmp GROUP BY [ISSUEDATE] HAVING Count(*)>1 )))
ORDER BY TABLENAME.ISSUEDATE;

In the above it is checking Issuedate for Dupes alone. You had stated that the date would never be the same, so this would be your target to check. If by chance you need both let me know. However that should cover it.

 

[PHV]

Something like this ?
SELECT * FROM myTable A INNER JOIN
(SELECT issueID FROM myTable GROUP BY issueID HAVING Count(*)>1) B
ON A.issueID=B.issueID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244