Queries within queries - The mystery of the sub query

[fenneraj]

Hi,
I have a table with about 4000 records in it, i need to pull out some specific data but i am struggling. Here are the fields:
Client_Number (Primary Key)
Nf_Items
DONE
TSM (this is a name of a person)

I have this query so far.....
SELECT *
FROM tbl_Xtra_MED INNER JOIN tbl_Inst_Posn
ON tbl_Xtra_MED.Client_Number = tbl_Inst_Posn.Client_Number
WHERE tbl_Inst_Posn.Client_Number NOT IN(SELECT Client_Number FROM tbl_Inst_Posn WHERE TSM <> 'Wallace, Andrew' AND Nf_Items < 5)
Order By tbl_Inst_Posn.TSM DESC

Although this produces a nice list of people and the no_of items done. It does not seem to filter out records that have a TSM value of 'Wallace, Andrew' and less the 5 items.


Can anyone shed any light on this matter????

Regards
Andrew Fenner

 

[PruSQLer]

Maybe I'm missing something but why do you need a subquery? Can't you just code:

Where (tbl_Inst_Posn.TSM <> 'Wallace, Andrew' AND Nf_Items < 5)

 

[nigelrivett]

Run the subquery and see what it returns.
Those Client_Number's should be missing from your result set unless it includes a null which may mess things up.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[RiverGuy]

You said you want that name filtered OUT, correct? Well, I believe the subquery should look like this:

\NOT IN(SELECT Client_Number FROM tbl_Inst_Posn WHERE TSM = 'Wallace, Andrew' OR No_Of_Items < 5)

This says don't include client numbers with that name, and don't include client numbers with no of items < 5.

 

[MakeItSo]

Agreed with River Guy:
NOT IN(SELECT ... <>'Wallace

is a double negation! YOu select those that are not (not equal to)

Either leave away the NOT from NOT IN, or change the subquery...
;-)

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de