asked on

Filter and condition needed to a SQL query

Attempting to pull some data from a database. I am currently look for program_id's of 400 or 250. I need to add conditions where I only want to return rows that contain 400's or 250's if both exist for a specific client_id. Each row only contains a 400 or 250 so I have been attempting to do nested queries with no luck.

select
      [tbl_client_program].[client_id],
      [tbl_client_program].[program_id],
      [tbl_client_program].[program_from_date],
      [tbl_client_program].[program_thru_date],
[kid combo table].[CLFIRST],
[kid combo table].[CLLAST]
from
      [dbo].[tbl_client_program] [tbl_client_program]
            inner join [dbo].[KID COMBO TABLE] [KID COMBO TABLE]
            on [tbl_client_program].[client_id] = [KID COMBO TABLE].[CTRLNO]
where
      ([tbl_client_program].[program_id] = 400) OR
([tbl_client_program].[program_id] = 250)

Patrick Matthews

Your syntax looks fine to me.

Are you 100% sure that you have data in those tables that meet those conditions?

Richard Quadling

Maybe something like ...

SELECT
	CP.client_id,
	CP.program_id,
	CP.program_from_date,
	CP.program_thru_date,
	KC.CLFIRST,
	KC.CLLAST
FROM
	dbo.tbl_client_program CP
	INNER JOIN
	dbo.[KID COMBO TABLE] KC ON
		CP.client_id = KC.CTRLNO
WHERE
	CP.client_id IN
		(
		SELECT
			T250.client_id
		FROM
			(
			SELECT DISTINCT
				client_id
			FROM
				tl_client_program
			WHERE
				program_ID = 250
			) T250
			INNER JOIN
			(
			SELECT DISTINCT
				client_id
			FROM
				tl_client_program
			WHERE
				program_ID = 400
			) T400 ON
			T250.clientid = T400.clientid
		)

Open in new window

RobertGates

ASKER

RQuadling: I tried your solution but am getting back results that are not 400 or 250s.
Just to confirm I only want to see client entries where they have a 400 and 250.

The resulting data would look like below for any clients that have both entries.

1234 400 1/1/11 4/1/11
1234 250 4/1/11 5/1/11

Anthony Perkins

Something like this perhaps:

SELECT  c.client_id,
        c.program_id,
        c.program_from_date,
        c.program_thru_date,
        k.CLFIRST,
        k.CLLAST
FROM    dbo.tbl_client_program c
        INNER JOIN dbo.[KID COMBO TABLE] k ON c.client_id = k.CTRLNO
        INNER JOIN (
		SELECT	Client_id
		FROM	(
			SELECT	Client_id
			FROM	dbo.tbl_client_program
			WHERE	program_id IN (400, 250)
			GROUP BY
				Client_id,
				program_id
			) c1 
		GROUP BY
			Client_id
		HAVING	COUNT(*) = 2) c2 ON c.Client_id = c2.Client_id

Open in new window

ASKER CERTIFIED SOLUTION

Richard Quadling

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial