StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODisplaying results based on record count using SQL
text
Body
copied!<p>I have database called schoolDB and 2 database tables, </p> <p><code>student</code> and <code>education</code></p> <p><strong>Create student table:</strong></p> <pre><code>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 </code></pre> <p><strong>Create Education table:</strong></p> <pre><code>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 </code></pre> <p>Here is a screenshot of the data:</p> <p><img src="https://i.stack.imgur.com/qQzyN.png" alt="enter image description here"></p> <p>I want to be able to find every one who has been to an institution name called <code>Secondary School</code> AND who has another education record with a course name <code>like</code> <code>biol</code>. Not just limited to biology, i want to find all the sciences, so i need to put multiple like statements.</p> <p><strong>I have tried this:</strong></p> <pre><code>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 </code></pre> <p><strong>SQL FIDDLE:</strong> <a href="http://sqlfiddle.com/#!3/666f8/2" rel="nofollow noreferrer">http://sqlfiddle.com/#!3/666f8/2</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload