SQL Extracting more data than I whant

[mych]

I have the following SQL...

SELECT M.* FROM tblMainData AS M WHERE M.DateProcessed IS NULL AND M.FIRefNo IN (SELECT T.FIRefNo FROM tblTopFI AS T WHERE T.ProductType = "CAS");

tblTopFI stores the top FI's for a particular product. Here is an extract of the data

RefNo ProductType FIRefNo
1 CAS Q12834/01
2 CAS Q4563/01
3 TEX Q1298/01
4 HBQ Q12327/01
5 PEC Q45382/01
6 CAS Q34234/02
11 OSA Q12327/01
12 PPS Q12327/01
13 NIT Q34234/01
17 PEC Q45454/01

When I run the above qry the output I get for the CAS product is...

ID DateReceived ProductType FIRefNo DateProcessed
10999 27/03/04 CAS Q12834/01
11002 27/03/04 S23 Q12834/01
11003 28/03/04 S23 Q4563/01
11012 01/04/04 S10 Q34234/02
11015 05/04/04 S10 Q12834/01

As you can see it has extracted records for S23 Products and S10 products even though none of the FIRef's are TopFI's for S23 and S10 products.

Any Help would be appreciated

 

[winston1983]

What are you actually trying to get?

Regards,


Lewis Harvey
lewis@lharvey.com

 

[mych]

The SQL should give me all the FIRefNo records in TblMainData for a particular ProductType AND who's DateProcessed is Null BUT the FIRefNo must also be present in the TblTopFI for that ProductType....

Wow is that a mouthful...

Hope you can Help


Mych

 

[winston1983]

It is returning al the values because you are not filtering down the CAS product type in the main query. Try something like:

SELECT * FROM TblMainData WHERE ProductType = 'CAS' AND DateProcessed = NULL AND M.FIRefNo IN (SELECT T.FIRefNo FROM tblTopFI WHERE ProductType = 'CAS');

I think that should work.

Regards,


Lewis Harvey
lewis@lharvey.com

 

[winston1983]

Has that worked?

Regards,


Lewis Harvey
lewis@lharvey.com

 

[mych]

Wow fast reply...

You are a gentleman and a star... worked great