filtering data

[macroapa]

Hi i have a table as follows:

Table1

Ref Code
123 1
123 4
123 5
123 6
321 1
321 8
987 6
777 3
777 2

Now what i want to do is return Ref where it has never been '4' ie
return

321
987
777

and exclude 123 as at some point 123 was 4.

Any help appreciated.

Thanks.

 

[macroapa]

Hi i have a table as follows:

Table1

Ref     Code
123     1
123     4
123     5
123     6
321     1
321     8
987     6
777     3
777     2

Now what i want to do is return Ref where it has never been '4'  ie
return

321
987
777

and exclude 123 as at some point 123 was 4.

Any help appreciated.

Thanks.

I think i may have it now!

SELECT DISTINCT Table1.Ref
FROM Table1
where table1.ref <> (SELECT DISTINCT Table1.Ref
FROM Table1
WHERE (((Table1.Code)=4))
;

 

[Dirk Goldgar]

Hi i have a table as follows:

Table1

Ref Code
123 1
123 4
123 5
123 6
321 1
321 8
987 6
777 3
777 2

Now what i want to do is return Ref where it has never been '4' ie
return

321
987
777

and exclude 123 as at some point 123 was 4.

Any help appreciated.

Thanks.

This is probably not the most efficient way to do it, but this SQL should
work:

SELECT DISTINCT Ref FROM Table1
WHERE Ref NOT IN(
SELECT REF FROM Table1 WHERE Code = 4)