Displaying results based on record count using SQL

Question

I have database called schoolDB and 2 database tables,

student and education

Create student table:

USE [schoolDB]
GO

/****** Object:  Table [dbo].[tblStudent]    Script Date: 09/22/2013 17:30:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblStudent](
    [STUDENTNUMBER] [varchar](50) NOT NULL,
    [STUDENTNAME] [varchar](50) NULL,
    [EDUCATIONID] [varchar](50) NULL,
 CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED 
(
    [STUDENTNUMBER] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Create Education table:

USE [schoolDB]
GO

/****** Object:  Table [dbo].[tblEducation]    Script Date: 09/22/2013 17:31:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblEducation](
    [EDUCATIONID] [varchar](50) NOT NULL,
    [STUDENTNUMBER] [varchar](50) NULL,
    [INSTITUTIONNAME] [varchar](50) NULL,
    [COURSENAME] [varchar](50) NULL,
    [GRADE] [varchar](50) NULL,
    [YEAROFLEAVING] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Here is a screenshot of the data:

I want to be able to find every one who has been to an institution name called Secondary School AND who has another education record with a course name like biol. Not just limited to biology, i want to find all the sciences, so i need to put multiple like statements.

I have tried this:

SELECT COUNT(*) AS 'Our Students', 
       DTOurStudents.STUDENTNAME 
FROM   (SELECT TOP 2 TBLSTUDENT.STUDENTNUMBER, 
                     TBLSTUDENT.STUDENTNAME, 
                     TBLEDUCATION.INSTITUTIONNAME, 
                     TBLEDUCATION.COURSENAME 
        FROM   TBLEDUCATION 
               INNER JOIN TBLSTUDENT 
                       ON TBLEDUCATION.STUDENTNUMBER = TBLSTUDENT.STUDENTNUMBER 
        WHERE  TBLEDUCATION.INSTITUTIONNAME LIKE '%Secondary School%') 
       DTOurStudents 
GROUP  BY DTOurStudents.STUDENTNAME

SQL FIDDLE: http://sqlfiddle.com/#!3/666f8/2

Answer 1

This will give you a list of students and a count of college courses (per college), by joining the institution table with itself.

SELECT
  STUDENTNUMBER,
  SCHOOL_NAME,
  COLLEGE_NAME,
  count(*) as COLLEGE_COURSES
FROM (
  SELECT    
    school.STUDENTNUMBER,
    school.INSTITUTIONNAME AS SCHOOL_NAME,
    college.INSTITUTIONNAME AS COLLEGE_NAME
  FROM dbo.tblEducation as school
  INNER JOIN dbo.tblEducation as college ON school.STUDENTNUMBER = college.STUDENTNUMBER
  WHERE school.INSTITUTIONNAME = 'Secondary School'
    AND college.INSTITUTIONNAME <> 'Secondary School'
    AND (college.COURSENAME like 'biol%'
         OR college.COURSENAME like 'math%'
         OR college.COURSENAME like 'etc%')
) AS c
GROUP BY STUDENTNUMBER, SCHOOL_NAME, COLLEGE_NAME

If you want the college coursename then you can return that in the inner query. But since there is only one record per college course, the outer select and the group by would be redundant.

SELECT  
  school.STUDENTNUMBER,
  school.INSTITUTIONNAME AS SCHOOL_NAME,
  college.INSTITUTIONNAME AS COLLEGE_NAME,
  college.COURSENAME
FROM dbo.tblEducation as school
INNER JOIN dbo.tblEducation as college ON school.STUDENTNUMBER = college.STUDENTNUMBER
WHERE   school.INSTITUTIONNAME = 'Secondary School'
  AND college.INSTITUTIONNAME <> 'Secondary School'
  AND  (college.COURSENAME like 'biol%'
          OR college.COURSENAME like 'math%'
          OR college.COURSENAME like 'etc%'

Answer 2

A simple answer.

1. I select all rows of interest (i.e. 'Secondary School' or by coursename).
2. I count the number of rows per student.
3. I filter to keep the students that have at least 2 rows.
4. I use an expression in the ORDER BY to make sure secondary school shows up first.

What your question left unclear is what should happen when there are more than 2 rows. In this case they all show up, but the query is easy enough to adjust (add a row_number and filter on rn <= 2).

Fiddle: http://sqlfiddle.com/#!3/666f8/89/0

WITH cte as (
  SELECT  
    STUDENTNUMBER, 
    COURSENAME, 
    INSTITUTIONNAME,
    COUNT(*) OVER (PARTITION BY STUDENTNUMBER) AS RecordCount
  FROM tblEducation
  WHERE INSTITUTIONNAME = 'Secondary School'
     OR COURSENAME like 'biol%'
     OR COURSENAME like 'math%'
     OR COURSENAME like 'etc%'
)
select *
from cte
where RecordCount >= 2
order by 
  studentnumber, 
  case when institutionname = 'Secondary School' then 1 else 2 end

EDIT

A comment correctly points out that the query doesn't check that there is at least one secondary school and one other education. There could be two secondary schools, or no secondary school at all!

Those cases can be handled with the slightly more complicated query below:

WITH cte as (
  SELECT  
    STUDENTNUMBER, 
    COURSENAME, 
    INSTITUTIONNAME,
    SUM(CASE INSTITUTIONNAME WHEN 'Secondary School' THEN 1 END) 
      OVER (PARTITION BY STUDENTNUMBER) AS SecondarySchoolCount,
    SUM(CASE WHEN INSTITUTIONNAME <> 'Secondary School' 
              AND COURSENAME LIKE 'biol%' 
             THEN 1 END) 
      OVER (PARTITION BY STUDENTNUMBER) AS CourseCount
  FROM tblEducation
  WHERE INSTITUTIONNAME = 'Secondary School'
     OR COURSENAME like 'biol%'
     OR COURSENAME like 'math%'
     OR COURSENAME like 'etc%'
)
select *
from cte
where SecondarySchoolCount >= 1 AND CourseCount >= 1
order by 
  studentnumber, 
  case when institutionname = 'Secondary School' then 1 else 2 end