SQL query need to get user where program column includes certain program

Question

Let me try to explain this to the best of my ability:

I have a table in which there is a userID column and a program column (along with other columns of non importance). I need to find the users within this table that have multiple instances within this table where that user has a program of X associated with it.

Can anyone help me please?

What I have so far is:

SELECT 
   WPP.USERID 
  FROM 
   WEBPROGRAMPARTICIPANTS WPP 
  INNER JOIN 
   WEBPROGRAMS WP 
  ON 
   WPP.PROGRAMCODE = WP.PROGRAMCODE 
  WHERE 
   CONFIRMED = 1 AND 
   WP.PROGRAMTYPE IN ('1') AND 
   WP.PROGRAMSTARTDATE >= '2000-01-01' AND 
   WPP.PROGRAMCODE = 'CL2010'
  GROUP BY 
   WPP.USERID 
  HAVING 
   COUNT(WPP.PROGRAMCODE) > 1

Answer 1

I think all your missing is group by wpp.programcode has well

SELECT 
                        WPP.USERID 
                FROM 
                        WEBPROGRAMPARTICIPANTS WPP 
                INNER JOIN 
                        WEBPROGRAMS WP 
                ON 
                        WPP.PROGRAMCODE = WP.PROGRAMCODE 
                WHERE 
                        CONFIRMED = 1 AND 
                        WP.PROGRAMTYPE IN ('1') AND 
                        WP.PROGRAMSTARTDATE >= '2000-01-01' AND 
                        WPP.PROGRAMCODE = 'CL2010'
                GROUP BY 
                        WPP.USERID , WPP.PROGRAMCODE
                HAVING 
                        COUNT(WPP.PROGRAMCODE) > 1

If you don't want to limit it to programcode cl2010 drop it from the where clause. I would also add wpp.programcode to you select list so you can see what codes are duplicated and count(wpp.programcode) if you want to see how many times.

Answer 2

DECLARE @Program VARCHAR(MAX)

SET @Program = 'X'

SELECT UserID, COUNT(Program) Duplicates
FROM Table
WHERE Program = @Program
AND COUNT(Program) > 1
GROUP BY UserID

Answer 3

You are probably going to have to do this as a subquery since once you add in a where clause limiting your results to a specific program type you will only be able to find participants who have multiple rows of that program type. You are probably looking for something like this:

SELECT 
    WPP.USERID 
FROM 
    WEBPROGRAMPARTICIPANTS WPP 
INNER JOIN 
    WEBPROGRAMS WP ON WPP.PROGRAMCODE = WP.PROGRAMCODE 
WHERE 
    CONFIRMED = 1 AND 
    WP.PROGRAMTYPE IN ('1') AND 
    WP.PROGRAMSTARTDATE >= '2000-01-01' AND 
    WPP.PROGRAMCODE = 'CL2010'
    WPP.USERID IN (
     SELECT 
      WPP.USERID
     FROM
      WEBPROGRAMPARTICIPANTS WPP
     GROUP BY 
      WPP.USERID 
     HAVING 
      COUNT(WPP.PROGRAMCODE) > 1)
GROUP BY
    WPP.USERID

Answer 4

I'll assume it's not finding any duplicates even though you know them to exist. With your query, if your duplicate has a different type, code, or start date before 2000-1-1, then those records will be filtered out before you run your count. Keep in mind that it's going to apply the "where" pretty early on in the process.

Keep this processing order in mind: http://tinman.cs.gsu.edu/~raj/sql/node22.html