June 16, 2009 at 11:07 am
Hi Friends,
I need some help with a query.
I have a table Test that stores the marks of students.
========= Table Creation Script ================
CREATE TABLE [dbo].[test](
[Name] [char](10) NULL,
[Age] [int] NULL,
[Subject] [varchar](50) NULL,
[IsPassed] [char](1) NULL
) ON [PRIMARY]
This is the test data
=========== Test Data ==================
INSERT INTO TEST
SELECT 'Sally',10,'Science','Y'
UNION
SELECT 'Sally',10,'Maths','Y'
UNION
SELECT 'Mary',10,'Science','Y'
UNION
SELECT 'Mary',10,'Maths','N'
UNION
SELECT 'Jim',10,'Maths','N'
UNION
SELECT 'Tom',10,'Science','N'
UNION
SELECT 'Tom',10,'Maths','N'
I need to report all students who have passed in Science at the same time failed in any other subject. So for this test data the output will be Mary. I just need the Name and the Age in the output.
I did come up with a query. But wanted to check with you whether this was the best solution or whether there are any improvements to the query
============Query ==============
SELECT
A.NAME,A.AGE
FROM
(
SELECT NAME,AGE FROM Test
WHERE (Subject = 'Science' AND IsPassed ='Y') ) A
INNER JOIN
(SELECT NAME,AGE FROM Test
WHERE Subject <> 'Science' and IsPassed ='N') B
ON A.NAME = B.NAME
AND A.AGE = B.AGE
This is going to hit a AS400 database so wanted to make sure that the query will not be resource intensive
Thanks
Vinu Verma
June 16, 2009 at 12:21 pm
In Books Online, check out Exists. If you use Where Exists and a sub-query to find non-science fails, and Where Exists and a sub-query to find science passes, you'll get what you need.
Your query should work, but it might produce duplicate results because of row-multiplication.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply