Sql Help

[rikki]

i have a table A and table B

 

table A looks like 

 

ID     Date                  ErrID

1       2014-04-24       '11'

1       2014-04-24       '21'

1       2014-04-24       '22'

1       2014-04-24       '99'

1       2014-02-11       '99'

2       2014-01-19       '22'

2       2014-01-19       '99'

2       2014-03-20       '99'

3       2014-04-24       '21'

3       2014-04-24       '99'

4       2014-04-24       '11'

4       2014-04-24       '22'

  

 

table B looks like 

 

ID     Date

1      2014-04-24

2      2014-03-20

3      2014-04-24

4      2014-04-24

 

i want output to be below. table B lo unna ID and Date table A tho match iyinapudu if ErrID is not (21 or 22) results kavali..   CITI#H@

if ID has all three '11' ,'2'1 and '99' with that date its not valid 

if ID has '11' and '99' its valid

if ID has '11' and '22' its valid

if ID has '22' and '99' its valid

 

 

output

ID     

2       '

3

4

[pythonic]

select a.ID, a.errID from A a, B b where a.ID = b.ID and a.Date = b.Date and a.ErrID not in ('21', '22');

[vadapav]

[rikki]

kaadu...edi ID:1 unnadi kuda return chestundi 

select a.ID, a.errID from A a, B b where a.ID = b.ID and a.Date = b.Date and a.ErrID not in ('21', '22');

 

[rikki]

 

[150bryant]

select  a.id, Max(Errid), Max(Date) from table A , Table B 

where A.id = B.id 

and Errid not in (21,22)

group by a.id

 

or 

 

 

 

select  a.id, Max(Errid) from table A , Table B 

where A.id = B.id 

and Errid not in (21,22)

group by a.id

 

[rikki]

edi 1's tho return chestundi bhaya 

ID     ErrID

1       '11'

1       '99'

2       '99'

select  a.id, Max(Errid), Max(Date) from table A , Table B 

where A.id = B.id 

and Errid not in (21,22)

group by a.id

 

or 

 

 

 

select  a.id, Max(Errid) from table A , Table B 

where A.id = B.id 

and Errid not in (21,22)

group by a.id

 

[pythonic]

kaadu...edi ID:1 unnadi kuda return chestundi 

errID varchar aa kada? or int ayithe try not in (21, 22);

[pythonic]

Got it try this man

select a.ID, a.errID from A a, B b where a.ID = b.ID and a.Date = b.Date and a.ID not in (select ID from A where ErrID in ('21', '22'));

[halwaraaj]

select a.ID, a.errID from A a, B b where a.ID = b.ID and a.Date = b.Date and a.ErrID not in ('21', '22');

 

 

select  a.id, Max(Errid), Max(Date) from table A , Table B 

where A.id = B.id 

and Errid not in (21,22)

group by a.id

 

or 

 

 

 

select  a.id, Max(Errid) from table A , Table B 

where A.id = B.id 

and Errid not in (21,22)

group by a.id

 

Athanu cheppedi.......21 and 22 unna records ravodhu ani kaadu......21 and 22 ki edaina ID associate ayyi unte aa ID vi ye records ravodhu......for ex: since ID:1 has ErrID:21 and 22, ID:1 ErrID: 11 and ID:1 ErrID:99 should also not show in results

[rikki]

varchar bhya kani ID 1 lo ErrID '11' undi kanuka adi kuda include itundi

 

edi 1's tho return chestundi bhaya 

ID     ErrID

1       '11'

1       '99'

2       '99'

 

 

 

errID varchar aa kada? or int ayithe try not in (21, 22);

 

[pythonic]

Athanu cheppedi.......21 and 22 unna records ravodhu ani kaadu......21 and 22 ki edaina ID associate ayyi unte aa ID vi ye records ravodhu......for ex: since ID:1 has ErrID:21 and 22, ID:1 ErrID: 11 and ID:1 ErrID:99 should also not show in results

Yeah naku taruvatha ardham ayindi anduke vere post vesa man...

[pythonic]

varchar bhya kani ID 1 lo ErrID '11' undi kanuka adi kuda include itundi

select a.ID, a.errID from A a, B b where a.ID = b.ID and a.Date = b.Date and a.ID not in (select ID from A where ErrID in ('21', '22'));

 

check this..

[rikki]

bhaya nenu output lo chepanu anda dani pina kuda same rasanu  $s@d

Athanu cheppedi.......21 and 22 unna records ravodhu ani kaadu......21 and 22 ki edaina ID associate ayyi unte aa ID vi ye records ravodhu......for ex: since ID:1 has ErrID:21 and 22, ID:1 ErrID: 11 and ID:1 ErrID:99 should also not show in results

 

[rikki]

not in place lo IN inner query place lo NOT in rasi thapu chesa epativaraku thanks lufyy bhayoo ee gif neeke

select a.ID, a.errID from A a, B b where a.ID = b.ID and a.Date = b.Date and a.ID not in (select ID from A where ErrID in ('21', '22'));

 

check this..