Unmatched query - not working

[George]

I am trying to set up an unmatched query but it does not work. I have two
queries and i am trying to match off with stock codes.
The first query has all the stock codes already in it (and no stock names).

The second query (only has stock names) adds the stock codes which it
sources from a table that contains both stock codes and the stock names.

Any idea why there is no output with this unmatched query.
Thanks
George

 

[Ken Sheridan]

George:

A possible reason is that you are placing a restriction on the second query
in addition to the IS NULL criterion. The combination of an IS NULL key and
a value in another column is self-contradictory so must evaluate to FALSE,
which is why no rows would be returned. Try using the NOT EXISTS predicate
against a subquery instead, e.g.

SELECT *
FROM FirstQuery
WHERE NOT EXISTS
(SELECT *
FROM SecondQuery
WHERE SecondQuery.StockCode = FirstQuery.StockCode);

Ken Sheridan
Stafford, England

 

[Ofer Cohen]

Does each query, running separetly, return records?

Can you post the SQL of the UnMatch query?

How did you get to the situation that the stock name is stored in the table
and not the stock code?
You should always store the code and not the description in related tables.

 

[George]

Hi Ken,
THanks for your tip, however everytime i try to open the following query the
database crashes and goes into recovery mode:

SELECT *
FROM [Axys holdings]
WHERE NOT EXISTS
(SELECT *
FROM [State 2]
WHERE [Axys holdings].[Stock code]= [State 2].[Axys stock code] );

any ideas?
George

 

[George]

Hi Ofer,
I posted the following for Ken, but haven't heard back. Hopefully you can
help me out here?

Hi Ken,
THanks for your tip, however everytime i try to open the following query the
database crashes and goes into recovery mode:

SELECT *
FROM [Axys holdings]
WHERE NOT EXISTS
(SELECT *
FROM [State 2]
WHERE [Axys holdings].[Stock code]= [State 2].[Axys stock code] );

any ideas?
George