1

I have a Select Statement that pulls all of the Publication by the ID that have Multiple entries. Having multiply entries is what I'm looking for so I can determine with the authors work for the same program/department or a different one. I'm not interested in single authors. This give me all of the Publications that are written in the same program.

SELECT        TOP (100) PERCENT dbo.[Publication.Person.Include_queue].PMID
FROM            dbo.[Publication.Person.Include_queue] INNER JOIN
                         dbo.ADMIN_Membership ON dbo.[Publication.Person.Include_queue].MemberID = dbo.ADMIN_Membership.MemberID
WHERE        (dbo.[Publication.Person.Include_queue].dbid = 750)
GROUP BY dbo.[Publication.Person.Include_queue].PMID, dbo.ADMIN_Membership.Program_Code
HAVING        (COUNT(*) > 1)
ORDER BY dbo.[Publication.Person.Include_queue].PMID

What I need is all PMID's for Publications that have a count of greater than 1 with different Program_code's. I think I need to create a sub query but not sure how to do that with structure.

So something like this fake pseudo code I think is what I need:

 Select PMID Where PMID.Count > 1 and Program_Code,Count > 1

I looked at How to get multiple counts with one SQL query? and it's close but not quite.

Bill
  • 1,423
  • 2
  • 27
  • 51

1 Answers1

2

Perhaps you should be asserting that the distinct count of program codes for a matching PMID is greater than one:

HAVING COUNT(DISTINCT Program_Code) > 1

Your updated query:

SELECT
    i.PMID
FROM dbo.[Publication.Person.Include_queue] i
INNER JOIN dbo.ADMIN_Membership m
    ON i.MemberID = m.MemberID
WHERE
    i.dbid = 750
GROUP BY
    i.PMID
HAVING
     COUNT(DISTINCT m.Program_Code) > 1
ORDER BY
    i.PMID;

Note that I also introduced table aliases into your query, which leave it much easier to read. Consider using aliases if you be concerned about the longevity of your SQL code.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360