CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Tarun. P
Tarun. P

Reputation: 442

join query within same table

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.

Upvotes: 2

Views: 50

Answers (6)

h0r53
h0r53

Reputation: 3219

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

Upvotes: 2

Cato
Cato

Reputation: 3701

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

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 0

Martin
Martin

Reputation: 16423

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.

Upvotes: 2

Andomar
Andomar

Reputation: 238048

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

Upvotes: 2

Related Questions