Help with NOT EXISTS query

[RDRaider]

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.

[John Bell]

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" <rd******@sbcglobal.net> wrote in message
news:AQ****************@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.

[RDRaider]

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" <jb************@hotmail.com> wrote in message
news:_r*********************@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" <rd******@sbcglobal.net> wrote in message
news:AQ****************@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.

[John Bell]

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" <rd******@sbcglobal.net> wrote in message
news:En****************@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" <jb************@hotmail.com> wrote in message
news:_r*********************@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" <rd******@sbcglobal.net> wrote in message
news:AQ****************@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.

[RDRaider]

Thank you, that works!

"John Bell" <jb************@hotmail.com> wrote in message
news:iw*********************@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" <rd******@sbcglobal.net> wrote in message
news:En****************@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" <jb************@hotmail.com> wrote in message
news:_r*********************@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" <rd******@sbcglobal.net> wrote in message
news:AQ****************@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.
>
>

[--CELKO--]

> 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.