join query within same table

user_1521171 0
Viewed 46

I have table errorflag where following is the data

+-----+-----+-------+
| ID  | NO  | Error |
+-----+-----+-------+
| 200 | 100 |     1 |
| 201 | 100 |     0 |
| 202 |  98 |     1 |
| 203 |  98 |     1 |
| 204 |  99 |     1 |
| 205 |  87 |     1 |
| 206 |  87 |     0 |
| 207 |  90 |     1 |
+-----+-----+-------+

i would like to have data output as below

No

98
99
90

when the Number is repeated in field 'NO' and error is 1 and 0 than i want to exclude from result.

I am struggling with joins and having count query.

sql-server-2008 sql-server sql
6 Answers
select  distinct no
from    YourTable yt1
where   not exists
        (
        select  *
        from    YourTable yt2
        where   yt1.no = yt2.no
                and yt2.error = 0
        )

Example at dbfiddle.co.uk

user_505520

I'm not 100% clear on what is being asked.

If I am right in thinking you only want NO values where there is no 0 for the error, then this should do the trick:

SELECT `NO`
  FROM errorflag
  WHERE `NO` NOT IN (
                  SELECT `NO`
                    FROM errorflag 
                    WHERE Error = 0)
  GROUP BY `NO`

Output:

NO
90
98
99

Sqlfiddle is here.

user_21697620

You could also do this in a single query if you use aggregate functions. Based on your described environment, Error can only hold the value of 0 or 1. Thus you could just group by NO and ensure that the minimum value for Error is never zero.

SELECT NO FROM TableName
GROUP BY NO
HAVING MIN(Error) <> 0
user_51994180

could be you can check for errono count = 2 and distinct error = 2 

  select e.* from errorflag e
  left join (
    select  errono, count(*) num_err_no, count(distinct error) count_error 
    from errorflag 
    group by errono 
    having count(*) =2 and count_error = 2
  ) t on t.errorno = e.errorno 
  where t.errorno is null
user_35223120

If the error flag has always 0 or 1 then you use not exists :

select ef.*
from errorflag ef
where not exists (select 1 
                  from errorflag ef1 
                  where ef1.no = ef.no and ef1.error <> ef.error
                 );

For only single column then only aggregation is enough :

select no
from errorflag ef
group by no
having count(distinct error) = 1;
user_55294450

to exclude IDs where a 01 pair exists for NO - try the following

SELECT DISTINCT NO FROM errorflag  
WHERE ID NOT IN 
            (SELECT e1.ID FROM errorflag e1 
                        JOIN errorflag e2 
                            ON (e1.Error = 0 And e2.error = 1 or e2.Error = 0 And e1.error = 1) AND e1.[No] = e2.[no]
            )

is there am maximum of 2 errors per NO?

Could you get errors 0,1,1 for NO 99 for example?

see here with a change

http://sqlfiddle.com/#!18/2d2cf5/3

user_64090000
Related