Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Filter and condition needed to a SQL query

Posted on 2011-09-15
5
Medium Priority
?
308 Views
Last Modified: 2012-05-12
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)
0
Comment
Question by:RobertGates
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36543643
Your syntax looks fine to me.

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

Expert Comment

by:Richard Quadling
ID: 36543694
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

0
 

Author Comment

by:RobertGates
ID: 36544645
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36547114
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

0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 2000 total points
ID: 36548833
Ah. It will return all entries where the client has a 400 AND a 250. So if they have these, then the 300 and 999 will also come back...

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
                )
	AND
	CP.program_id IN (250, 400)

Open in new window


Just add
	AND
	CP.program_id IN (250, 400)

Open in new window

to the end of my original query.

Sorry for that.

0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question