December 11, 2004 at 10:37 am
I have one table with two field one is SerialNo and other is Pass/Fail
Here is my data. I have couple of unit which is fail first time and then pass second time.
100 Pass
200 Pass
300 Fail
300 Pass
400 Pass
500 Fail
500 Fail
500 Pass
600 fail
I need a query which give me all fail result but if it is pass second time i don't want a see those in this case i need only 600 fail back.
December 11, 2004 at 7:07 pm
Hi ,
The solution for your problem could be obtainted by the query below :
select a.id ,a.first_name from
table2 a
where
a.result='fail'
and a.id not in (select id from
table2 where id=a.id and
result='pass')
here first the resultset required is obtainted by using the condition in the where clause and then using Subquery to check if there are Pass results for the result obtainted.
Please let me know if you have any queries .
Suresh Ramakrishnan
December 11, 2004 at 7:47 pm
Here's a quick solution based on the info in your post, buddy. There is proly a less expensive way to do this, but here you go. Substitute the name of your table for 'YourTable'
SELECT DISTINCT a.SerialNo
FROM YourTable a
WHERE a.SerialNo NOT IN
(SELECT b.SerialNo
FROM YourTable b
WHERE b.PassFail = 'pass')
The subquery collects SerialNo's that 'pass' and tells the main query to show all SerialNo's that are NOT IN this collection. So you will see 1 record for each SerialNo that did not pass.
Hope this is helpful.
Matthew Spare
Matthew Spare
ithinkdesigns.com
December 12, 2004 at 1:42 pm
Thanks for your help guys. The first one work for me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply