Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to select records with diff values of col name

Author  Topic 

aksum
Starting Member

2 Posts
Posted - 2011-12-07 : 14:10:39
I am very new with sql and doing my best to follow FAQ of how to ask questions efficiently.

I am managing sql database of an educational institution.
I would like to select students who have taken both Continuing Ed and Bachelors classes.

My table: ACADEMIC (see below)
My collumn: Program
The values to select are 'UGRAD' and 'CONED'
Also, see below a select statement for sample data.

CREATE TABLE [dbo].[ACADEMIC](
[PEOPLE_CODE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PEOPLE_ID] [varchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PEOPLE_CODE_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ACADEMIC_YEAR] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ACADEMIC_TERM] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ACADEMIC_SESSION] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PROGRAM] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DEGREE] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CURRICULUM] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[COLLEGE] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DEPARTMENT] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CLASS_LEVEL] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NONTRAD_PROGRAM] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[POPULATION] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ADVISOR] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADMIT_YEAR] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADMIT_TERM] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADMIT_SESSION] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADMIT_DATE] [datetime] NULL,
[MATRIC] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MATRIC_YEAR] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MATRIC_TERM] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MATRIC_SESSION] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MATRIC_DATE] [datetime] NULL,
[FULL_PART] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACADEMIC_STANDING] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REGISTER_LIMIT] [numeric](6, 2) NOT NULL,
[EXPECT_GRAD_MM] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EXPECT_GRAD_YYYY] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ENROLL_SEPARATION] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SEPARATION_DATE] [datetime] NULL,
[CREDITS] [numeric](6, 2) NOT NULL,
[CREATE_DATE] [datetime] NOT NULL,
[CREATE_TIME] [datetime] NOT NULL,
[CREATE_OPID] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CREATE_TERMINAL] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[REVISION_DATE] [datetime] NOT NULL,
[REVISION_TIME] [datetime] NOT NULL,
[REVISION_OPID] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[REVISION_TERMINAL] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ABT_JOIN] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PREREG_VALIDATE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PREREG_VAL_WHO] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PREREG_VAL_DATE] [datetime] NULL,
[REG_VALIDATE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[REG_VAL_WHO] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REG_VAL_DATE] [datetime] NULL,
[GRADUATED] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[GRADUATED_YEAR] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GRADUATED_TERM] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GRADUATED_SESSION] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ORG_CODE_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ACADEMIC_FLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[APPLICATION_FLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[APP_STATUS] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[APP_STATUS_DATE] [datetime] NULL,
[APP_DECISION] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[APP_DECISION_DATE] [datetime] NULL,
[COUNSELOR] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COLLEGE_ATTEND] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACADEMIC_RATING] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADVOCATE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACA_PLAN_SETUP] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[STATUS] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TRANSCRIPT_SEQ] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LAST_ACTIVITY] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CURRENT_ACTIVITY] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[INQUIRY_FLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[INQUIRY_DATE] [datetime] NULL,
[FIN_AID_CANDIDATE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EXTRA_CURRICULAR] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEGREE_CANDIDATE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[INTEREST_LEVEL] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[INQ_STATUS] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[INQ_STATUS_DATE] [datetime] NULL,
[APPLICATION_DATE] [datetime] NULL,
[PRIMARY_FLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PROGRAM_START_DATE] [datetime] NULL,
[PROGRAM_END_DATE] [datetime] NULL,
[MET_ENGLISH_REQUIREMENT] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DROP_BELOW_FULL_CODE] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AUTH_START_DATE] [datetime] NULL,
[AUTH_END_DATE] [datetime] NULL,
[PROTECT_COUNSELOR] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [dfAcademicProtectCounselor] DEFAULT ('N'),
CONSTRAINT [ACADEMIC_PK] PRIMARY KEY CLUSTERED
(
[PEOPLE_CODE_ID] ASC,
[ACADEMIC_YEAR] ASC,
[ACADEMIC_TERM] ASC,
[ACADEMIC_SESSION] ASC,
[PROGRAM] ASC,
[DEGREE] ASC,
[CURRICULUM] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


P 000000006 P000000006 2005 FALL UGRAD BFA UND FOUN NULL 2005 FALL MAIN 2005-09-06 00:00:00.000 N NULL NULL NULL NULL FULL NULL 0.00 NULL NULL ENRL NULL 15.00 2009-05-27 00:00:00.000 1900-01-01 23:13:38.000 SCTCONV 0001 2009-06-12 00:00:00.000 1900-01-01 17:28:26.000 SCTCONV 0001 * N NULL NULL Y SCTCONV 2009-05-27 00:00:00.000 N NULL NULL NULL O000000001 Y N NULL NULL NULL NULL NULL FTF NULL NULL N A 001 N N N NULL NULL N N NULL NULL NULL NULL Y NULL NULL NULL NULL NULL NULL N
P 000000006 P000000006 2005 FALL MAIN UGRAD BFA UND FOUN NULL 2005 FALL MAIN 2005-09-06 00:00:00.000 N NULL NULL NULL NULL FULL NULL 0.00 NULL NULL ENRL NULL 15.00 2009-05-27 00:00:00.000 1900-01-01 23:13:38.000 SCTCONV 0001 2009-06-12 00:00:00.000 1900-01-01 17:28:26.000 SCTCONV 0001 * N NULL NULL Y SCTCONV 2009-05-27 00:00:00.000 N NULL NULL NULL O000000001 Y N NULL NULL NULL NULL NULL FTF NULL NULL N A 001 N N N NULL NULL N N NULL NULL NULL NULL Y NULL NULL NULL NULL NULL NULL N
P 000000006 P000000006 2006 SPR UGRAD BFA UND FOUN NULL 2005 FALL MAIN 2005-09-06 00:00:00.000 N NULL NULL NULL NULL FULL NULL 0.00 NULL NULL ENRL NULL 15.00 2009-05-27 00:00:00.000 1900-01-01 23:13:41.000 SCTCONV 0001 2009-06-12 00:00:00.000 1900-01-01 17:28:26.000 SCTCONV 0001 * N NULL NULL Y SCTCONV 2009-05-27 00:00:00.000 N NULL NULL NULL O000000001 Y N NULL NULL NULL NULL NULL RET NULL NULL N A 001 Y N N NULL NULL N N NULL NULL NULL NULL Y NULL NULL NULL NULL NULL NULL N
P 000000006 P000000006 2006 SPR MAIN UGRAD BFA UND FOUN NULL 2005 FALL MAIN 2005-09-06 00:00:00.000 N NULL NULL NULL NULL FULL NULL 0.00 NULL NULL ENRL NULL 15.00 2009-05-27 00:00:00.000 1900-01-01 23:13:41.000 SCTCONV 0001 2009-06-12 00:00:00.000 1900-01-01 17:28:26.000 SCTCONV 0001 * N NULL NULL Y SCTCONV 2009-05-27 00:00:00.000 N NULL NULL NULL O000000001 Y N NULL NULL NULL NULL NULL RET NULL NULL N A 001 N N N NULL NULL N N NULL NULL NULL NULL Y NULL NULL NULL NULL NULL NULL N
P 000000008 P000000008 2005 FALL CONED NON UND CE NULL 2005 NULL CONED 2005-09-06 00:00:00.000 N NULL NULL NULL NULL NULL NULL 0.00 NULL NULL ENRL NULL 1.00 2009-05-27 00:00:00.000 1900-01-01 23:13:41.000 SCTCONV 0001 2009-06-12 00:00:00.000 1900-01-01 17:28:26.000 SCTCONV 0001 * N NULL NULL Y SCTCONV 2009-05-27 00:00:00.000 N NULL NULL NULL O000000001 Y N NULL NULL NULL NULL NULL FTF NULL NULL N A 001 Y N N NULL NULL N N NULL NULL NULL NULL Y NULL NULL NULL NULL NULL NULL N
P 000000008 P000000008 2005 FALL CONED CONED NON UND CE NULL 2005 NULL CONED 2005-09-06 00:00:00.000 N NULL NULL NULL NULL NULL NULL 0.00 NULL NULL ENRL NULL 1.00 2009-05-27 00:00:00.000 1900-01-01 23:13:41.000 SCTCONV 0001 2009-06-12 00:00:00.000 1900-01-01 17:28:26.000 SCTCONV 0001 * N NULL NULL Y SCTCONV 2009-05-27 00:00:00.000 N NULL NULL NULL O000000001 Y N NULL NULL NULL NULL NULL FTF NULL NULL N A 001 N N N NULL NULL N N NULL NULL NULL NULL Y NULL NULL NULL NULL NULL NULL N
P 000000010 P000000010 2002 FALL UGRAD BFA ILLUST FOUN NULL 2003 FALL MAIN 2003-09-02 00:00:00.000 N NULL NULL NULL NULL FULL NULL 0.00 NULL NULL ENRL NULL 15.00 2009-05-27 00:00:00.000 1900-01-01 23:13:41.000 SCTCONV 0001 2009-06-12 00:00:00.000 1900-01-01 17:28:26.000 SCTCONV 0001 * N NULL NULL Y SCTCONV 2009-05-27 00:00:00.000 N NULL NULL NULL O000000001 Y N NULL NULL NULL NULL NULL FTF NULL NULL N A 001 N N N NULL NULL N N NULL NULL NULL NULL Y NULL NULL NULL NULL NULL NULL N

I would be happy to receive a critique of how I could contruct this better in future postings.
Thanks,
Hylon


MSSQL 2005 on Win2k3

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-12-07 : 14:25:20
SELECT PEOPLE_ID
FROM ACADEMIC
GROUP BY PEOPLE_ID
HAVING
SUM(CASE WHEN PROGRAM = 'CONED' THEN 1 ELSE 0 END) > 0
and SUM(CASE WHEN PROGRAM = 'UGRAD' THEN 1 ELSE 0 END) > 0


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

aksum
Starting Member

2 Posts
Posted - 2011-12-08 : 10:33:01
Perfect!
Thank you Jim,
Hylon

MSSQL 2005 on Win2k3
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-08 : 10:59:34
it can be merged as


SELECT PEOPLE_ID
FROM ACADEMIC
GROUP BY PEOPLE_ID
HAVING 
COUNT(DISTINCT CASE WHEN PROGRAM IN ('CONED', 'UGRAD')  THEN PROGRAM  ELSE NULL END) =2




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site