CODEWITHSUNDEEP
sqlsql-server
Cromon
Cromon

Reputation: 1

SQL: Finding where value appears more than once

I'm trying to find records where "subfield a" appears more than once.

This query works great for finding rows that have any subfield a at all:

SELECT Distinct 
    BT.BibliographicRecordID as recordid
FROM 
    BibliographicTags BT with (nolock)
JOIN 
    BibliographicSubfields BS with (nolock) ON BS.BibliographicTagID = BT.BibliographicTagID
WHERE 
    BT.TagNumber = 049
    AND BS.subfield ='a'

But I'm trying for something with the effect of:

SELECT Distinct 
    BT.BibliographicRecordID as recordid
FROM 
    BibliographicTags BT with (nolock)
JOIN 
    BibliographicSubfields BS with (nolock) ON BS.BibliographicTagID = BT.BibliographicTagID
WHERE 
    BT.TagNumber = 049
    AND BS.subfield = 'a' APPEARS MORE THAN ONCE

Thank you!

Upvotes: 0

Views: 2268

Answers (1)

sstan
sstan

Reputation: 36473

Maybe a simple GROUP BY and HAVING clause does the trick?

SELECT BT.BibliographicRecordID as recordid
FROM BibliographicTags BT with (nolock)
JOIN BibliographicSubfields BS with (nolock)
ON BS.BibliographicTagID = BT.BibliographicTagID
AND BS.subfield ='a'
WHERE BT.TagNumber = 049
GROUP BY BT.BibliographicRecordID
HAVING COUNT(*) > 1

BTW, make sure you have a very good reason to use the with (nolock) hints. It's generally not what you want.

Relevant: SQL Server NOLOCK Hint & other poor ideas.

EDIT:

Actually, without fully understanding your data model, the following query is more likely to give you the data as you expect:

SELECT Distinct BT.BibliographicRecordID as recordid
  FROM BibliographicTags BT
 WHERE BT.BibliographicTagID IN (
                SELECT BT.BibliographicTagID
                  FROM BibliographicTags BT
                  JOIN BibliographicSubfields BS
                    ON BS.BibliographicTagID = BT.BibliographicTagID
                   AND BS.subfield = 'a'
                 GROUP BY BT.BibliographicTagID
                HAVING COUNT(*) > 1)

EDIT 2

Here is the SQL Fiddle link to see the query in action. Hopefully you'll be able to spot what is different about your query? You are using SQL Server, right?

Upvotes: 1

Related Questions