Help with NOT EXISTS query

I am having trouble with what will surely be a simple query for you experts.

I have 2 tables with inventory data.
IMITMIDX contains the master item info
IMINVLOC contains location specific data such as quantity on hand at that
location.

These tables have 2 commons fields, ITEM_NO and LOC

I need to search the IMINVLOC table for any records where ITEM_NO and LOC do
not match that in the IMITMIDX table.

The following query give me zero records even though I can manually find
some records:

SELECT *
FROM IMINVLOC_SQL INNER JOIN
IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
IMINVLOC_SQL.loc)

Any ideas?
Thanks.Hi

It is better to post DDL ( CREATE TABLE statements etc...) and example data
( as Insert statements ) than a description of pseudo code.

Either

SELECT L.*
FROM IMINVLOC L
WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC )

OR

SELECT L.*
FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC
WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL

John

"RDRaider" <rdraider@.sbcglobal.net> wrote in message
news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> I am having trouble with what will surely be a simple query for you
experts.
> I have 2 tables with inventory data.
> IMITMIDX contains the master item info
> IMINVLOC contains location specific data such as quantity on hand at that
> location.
> These tables have 2 commons fields, ITEM_NO and LOC
> I need to search the IMINVLOC table for any records where ITEM_NO and LOC
do
> not match that in the IMITMIDX table.
> The following query give me zero records even though I can manually find
> some records:
> SELECT *
> FROM IMINVLOC_SQL INNER JOIN
> IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
> IMINVLOC_SQL.loc)
>
> Any ideas?
> Thanks.|||Thank you very much for your help. I'm getting closer, let me try to state
my problem more clearly.
Every record in IMITMIDX must have a matching record in IMINVLOC with the
same ITEM_NO and LOC. IMINVLOC can have multiple records for the same item
in IMITMIDX (each location has a record). The query you provided gives me
records with item_no and loc that don't match that in imitmidx.

Example data:
Table: IMITMIDX
Item_no Loc
BRONZE SD

Table: IMINVLOC
Item_no Loc
BRONZE GSN
BRONZE RMN
BRONZE NS
BRONZE SA
BRONZE SD
BRONZE VIS
BRONZE WSD
BRONZE RAW

Your query returns the following: (record with LOC = SD is not
returned)
BRONZE GSN
BRONZE RMN
BRONZE NS
BRONZE SA
BRONZE VIS
BRONZE WSD
BRONZE RAW

I need a query that will tell me when the IMINVLOC table does not contain
the same Item_no/Loc combination as the Imitmidx table.

Thanks again for the help.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> Hi
> It is better to post DDL ( CREATE TABLE statements etc...) and example
data
> ( as Insert statements ) than a description of pseudo code.
> Either
> SELECT L.*
> FROM IMINVLOC L
> WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC )
> OR
> SELECT L.*
> FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC
> WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> John
> "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > I am having trouble with what will surely be a simple query for you
> experts.
> > I have 2 tables with inventory data.
> > IMITMIDX contains the master item info
> > IMINVLOC contains location specific data such as quantity on hand at
that
> > location.
> > These tables have 2 commons fields, ITEM_NO and LOC
> > I need to search the IMINVLOC table for any records where ITEM_NO and
LOC
> do
> > not match that in the IMITMIDX table.
> > The following query give me zero records even though I can manually find
> > some records:
> > SELECT *
> > FROM IMINVLOC_SQL INNER JOIN
> > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
> > IMINVLOC_SQL.loc)
> > Any ideas?
> > Thanks.|||Hi

Maybe this way around?

SELECT M.*
FROM IMITMIDX M
WHERE NOT EXISTS ( SELECT * FROM IMINVLOC L WHERE M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC )

John

"RDRaider" <rdraider@.sbcglobal.net> wrote in message
news:EnZEc.7044$Ul1.576@.newssvr27.news.prodigy.com ...
> Thank you very much for your help. I'm getting closer, let me try to
state
> my problem more clearly.
> Every record in IMITMIDX must have a matching record in IMINVLOC with the
> same ITEM_NO and LOC. IMINVLOC can have multiple records for the same
item
> in IMITMIDX (each location has a record). The query you provided gives me
> records with item_no and loc that don't match that in imitmidx.
> Example data:
> Table: IMITMIDX
> Item_no Loc
> BRONZE SD
> Table: IMINVLOC
> Item_no Loc
> BRONZE GSN
> BRONZE RMN
> BRONZE NS
> BRONZE SA
> BRONZE SD
> BRONZE VIS
> BRONZE WSD
> BRONZE RAW
>
> Your query returns the following: (record with LOC = SD is not
> returned)
> BRONZE GSN
> BRONZE RMN
> BRONZE NS
> BRONZE SA
> BRONZE VIS
> BRONZE WSD
> BRONZE RAW
>
> I need a query that will tell me when the IMINVLOC table does not contain
> the same Item_no/Loc combination as the Imitmidx table.
> Thanks again for the help.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> > Hi
> > It is better to post DDL ( CREATE TABLE statements etc...) and example
> data
> > ( as Insert statements ) than a description of pseudo code.
> > Either
> > SELECT L.*
> > FROM IMINVLOC L
> > WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
> > AND M.LOC = L.LOC )
> > OR
> > SELECT L.*
> > FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> > AND M.LOC = L.LOC
> > WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> > John
> > "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> > news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > > I am having trouble with what will surely be a simple query for you
> > experts.
> > > > I have 2 tables with inventory data.
> > > IMITMIDX contains the master item info
> > > IMINVLOC contains location specific data such as quantity on hand at
> that
> > > location.
> > > > These tables have 2 commons fields, ITEM_NO and LOC
> > > > I need to search the IMINVLOC table for any records where ITEM_NO and
> LOC
> > do
> > > not match that in the IMITMIDX table.
> > > > The following query give me zero records even though I can manually
find
> > > some records:
> > > > SELECT *
> > > FROM IMINVLOC_SQL INNER JOIN
> > > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > > where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc
=
> > > IMINVLOC_SQL.loc)
> > > > > Any ideas?
> > > Thanks.
> >|||Thank you, that works!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:iwZEc.1256$Dv5.10834047@.news-text.cableinet.net...
> Hi
> Maybe this way around?
> SELECT M.*
> FROM IMITMIDX M
> WHERE NOT EXISTS ( SELECT * FROM IMINVLOC L WHERE M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC )
> John
> "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> news:EnZEc.7044$Ul1.576@.newssvr27.news.prodigy.com ...
> > Thank you very much for your help. I'm getting closer, let me try to
> state
> > my problem more clearly.
> > Every record in IMITMIDX must have a matching record in IMINVLOC with
the
> > same ITEM_NO and LOC. IMINVLOC can have multiple records for the same
> item
> > in IMITMIDX (each location has a record). The query you provided gives
me
> > records with item_no and loc that don't match that in imitmidx.
> > Example data:
> > Table: IMITMIDX
> > Item_no Loc
> > BRONZE SD
> > Table: IMINVLOC
> > Item_no Loc
> > BRONZE GSN
> > BRONZE RMN
> > BRONZE NS
> > BRONZE SA
> > BRONZE SD
> > BRONZE VIS
> > BRONZE WSD
> > BRONZE RAW
> > Your query returns the following: (record with LOC = SD is not
> > returned)
> > BRONZE GSN
> > BRONZE RMN
> > BRONZE NS
> > BRONZE SA
> > BRONZE VIS
> > BRONZE WSD
> > BRONZE RAW
> > I need a query that will tell me when the IMINVLOC table does not
contain
> > the same Item_no/Loc combination as the Imitmidx table.
> > Thanks again for the help.
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> > > Hi
> > > > It is better to post DDL ( CREATE TABLE statements etc...) and example
> > data
> > > ( as Insert statements ) than a description of pseudo code.
> > > > Either
> > > > SELECT L.*
> > > FROM IMINVLOC L
> > > WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO =
L.ITEM_NO
> > > AND M.LOC = L.LOC )
> > > > OR
> > > > SELECT L.*
> > > FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> > > AND M.LOC = L.LOC
> > > WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> > > > John
> > > > "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> > > news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > > > I am having trouble with what will surely be a simple query for you
> > > experts.
> > > > > > I have 2 tables with inventory data.
> > > > IMITMIDX contains the master item info
> > > > IMINVLOC contains location specific data such as quantity on hand at
> > that
> > > > location.
> > > > > > These tables have 2 commons fields, ITEM_NO and LOC
> > > > > > I need to search the IMINVLOC table for any records where ITEM_NO
and
> > LOC
> > > do
> > > > not match that in the IMITMIDX table.
> > > > > > The following query give me zero records even though I can manually
> find
> > > > some records:
> > > > > > SELECT *
> > > > FROM IMINVLOC_SQL INNER JOIN
> > > > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > > > where not exists (select loc from iminvloc_sql where
IMITMIDX_SQL.loc
> =
> > > > IMINVLOC_SQL.loc)
> > > > > > > > Any ideas?
> > > > Thanks.
> > > > > > >|||> I have 2 tables with inventory data. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

>> IMITMIDX contains the master item info;
IMINVLOC contains location specific data such as quantity on hand at
that
location. These tables have 2 common fields [sic], ITEM_NO and LOC <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. I would assume from this
narrative that IMITMIDX should not have a location at all, but only
information about the items -- UPC, size, weight, color, etc. and that
it would be referenced by the
IMINVLOC table for the quantity at each location (warehouses?,
stores?).

>> I need to search the IMINVLOC table for any records [sic] where
ITEM_NO and LOC do not match that in the IMITMIDX table. <<

>> The following query give me zero records [sic]though I can manually
find some records [sic] <<

Why did you put "_SQL" postfixes on the names in the query? Never use
SELECT * in production code; I have no choice because I have no DDL:

SELECT I1.*, L1.*
FROM Imitmidx AS I1
LERFT OUTER JOIN
IminvLoc AS L1
ON I1.item_no = L1.item_no
AND I1.loc = L1.loc;

This will give you NULLs for the unmatched rows.

Never use uppercase letters for names (it is unreadable; that is why
newspapers and books are mixed case). Get a copy of ISO-11179 and
starting using the standards for data element names, too.