Help with SQL query

Hi, I have a table like the following fields:

TradeDate Item Price

Now, suppose I want to do an update like this: every price
corresponding to a date higher than '30 Sep 2005' will be reset to the
latest price in that item. Can I do something like the following

UPDATE Table t SET Price = (SELECT TOP 1 Price FROM Table q WHERE
q.Item = t.Item ORDER BY TradeDate DESC) WHERE t.TradeDate > '30 Sep
2005'

or is there a better way?

Thank you very much.

BrunoHi Bruno,

Perfect :-D Looks good to me.

HTH, Jens Suessmeyer

Help with relational set division, please! Good challange!

/*
Please help.
Definition:
I am trying to compose a query that will do part matching based on certain
input criteria, by item class.
I want to define a table called criteria and let users define sets of
criteria that may satisfy a part match. if I have
an input set of parameters, i want to match this set to all defined criteria
sets. The first six define size matching, via range.
It is assumend that indexes of 1-2 is x size range, 3-4 is y size range and
5-6 is z size range. Therefore the first part is to ensure that
any xyz size part by class is to ensure that it fits XYZ specified criteria
ranges for that class.
I got that working below. Any parameter (index > 6) has to match exactly, no
ranges. Like if a part has material M1, matching set(s) have to have that
material as well, = input material.
The second requirement is that an input criteria must find record sets that
matches defined input exactly or can have extra elements in criteria,
that can be ignored, as long as it is not specified in input set.
I do understand that this is a case of set relational division.
I am trying to find sets that contain a specific subset.
If I get multiple sets matching, the top ranked (defined later)
ResultingItem (set) will be used as the final part match.
This seems to be the difficult part, escaping me.
Please help. Your effort and time is greatly appreciated.
farmer
In the example below, input set matches criteria for ItemA, not ItemX,
therefore ItemA is the match.
If input record index 10 would not exists, then both would match.
Appropriate characteristics have the same index (7 to N)
*/
set nocount on
declare @.criteria table
(
ItemClass varchar(10) not null
,indID int
,indValue varchar(50)
,ResultingItem varchar(10) not null
,primary key (ItemClass, indID, ResultingItem)
)
declare @.Input table
(
ItemClass varchar(10)
,indID int
,indValue varchar(50)
)
-- this is defined criteria
insert @.criteria values ('Panel', 1, '20', 'ItemA') -- x from
insert @.criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
insert @.criteria values ('Panel', 3, '564.75', 'ItemA') --y from
insert @.criteria values ('Panel', 4, '564.75', 'ItemA') --y to
insert @.criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
insert @.criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
insert @.criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined but
IMOS data will not have it
insert @.criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
insert @.criteria values ('Panel', 15, 'a', 'ItemA') -- other characteristic
insert @.criteria values ('Panel', 1, '20', 'ItemX') -- x from
insert @.criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
insert @.criteria values ('Panel', 3, '564.75', 'ItemX') --y from
insert @.criteria values ('Panel', 4, '564.75', 'ItemX') --y to
insert @.criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
insert @.criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
insert @.criteria values ('Panel', 10, 'A1', 'ItemX') -- color -- defined but
IMOS data will not have it
insert @.criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
insert @.criteria values ('Panel', 15, 'c', 'ItemX') -- other characteristic
-- in this set, duplicate input values for sizes so we have matching index
insert @.Input values ('Panel', 1, '20.5') -- x value
insert @.Input values ('Panel', 2, '20.5') -- x value, same as prior index
insert @.Input values ('Panel', 3, '564.75') --y value
insert @.Input values ('Panel', 4, '564.75') --y value, same as prior index
insert @.Input values ('Panel', 5, '6.0') -- z value
insert @.Input values ('Panel', 6, '6.0') -- z value, same as prior index
insert @.Input values ('Panel', 10, 'c1') -- color specied
insert @.Input values ('Panel', 14, 'M1') -- mat
-- Now, let us try to match
set nocount off
select c.ItemClass, c.ResultingItem, COUNT(*)
FROM @.criteria c
LEFT JOIN @.Input i
on c.ItemClass = i.ItemClass
AND c.indID = i.indID
AND (case when i.indID = 1 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 2 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID > 6 then c.indValue else i.indValue end) = i.indValue
GROUP BY c.ItemClass, c.ResultingItem--, c.ItemClass
--HAVING COUNT(*) <=
--AND COUNT(c.ItemClass) =
--(
-- SELECT COUNT(c2.ItemClass)
-- FROM @.criteria c2
-- WHERE c2.ItemClass = i.ItemClass
-- GROUP BY c2.ItemClass
--)
select *
from @.Input i
LEFT JOIN @.criteria c
--on c.ItemClass = i.ItemClass AND c.indID = i.indID
on c.ItemClass = i.ItemClass AND c.indID = i.indID
AND (case when i.indID = 1 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 2 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID > 6 then c.indValue else i.indValue end) = i.indValue
--SELECT PS1.pilot
--FROM PilotSkills AS PS1
--JOIN Hangar AS H1 ON PS1.plane = H1.plane
--GROUP BY PS1.pilot
--HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar);
--print 'Fourth way'
--SELECT PS1.pilot
--FROM PilotSkills AS PS1
--LEFT OUTER JOIN Hangar AS H1 ON PS1.plane = H1.plane
--GROUP BY PS1.pilot
--HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
--AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar);thank you all
I did come up with an answer. Any optimizations you see?
if object_id('tempdb..#criteria') is not null
drop table #criteria
go
if object_id('tempdb..#Input') is not null
drop table #Input
go
CREATE TABLE #Criteria
(
ItemFamily varchar(10) not null
,PropertyID int
,CriteriaValue varchar(50)
,ItemMatch varchar(10) not null
,PRIMARY KEY CLUSTERED (PropertyID, ItemFamily, ItemMatch)
)
CREATE TABLE #Input
(
ItemFamily varchar(10)
,PropertyID int
,CriteriaValue varchar(50)
,PRIMARY KEY CLUSTERED(PropertyID, ItemFamily)
)
go
set nocount on
-- this is defined criteria
insert #Criteria values ('Panel', 1, '20.50', 'ItemA') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemA') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemA') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
insert #Criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
insert #Criteria values ('Panel', 15, 'a', 'ItemA') -- other characteristic
insert #Criteria values ('Panel', 1, '20.00', 'ItemX') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemX') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemX') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
insert #Criteria values ('Panel', 10, 'a1', 'ItemX') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
insert #Criteria values ('Panel', 15, 'c', 'ItemX') -- other characteristic
insert #Criteria values ('Back', 1, '20.50', 'ItemZ') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemZ') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemZ') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemZ') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemZ') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemZ') -- z to
insert #Criteria values ('Back', 10, 'c1', 'ItemZ') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Back', 14, 'M1', 'ItemZ') -- mat
insert #Criteria values ('Back', 15, 'a', 'ItemZ') -- other characteristic
insert #Criteria values ('Back', 1, '20.00', 'ItemY') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemY') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemY') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemY') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemY') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemY') -- z to
insert #Criteria values ('Back', 10, 'a1', 'ItemY') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Back', 14, 'M1', 'ItemY') -- mat
insert #Criteria values ('Back', 15, 'c', 'ItemY') -- other characteristic
-- in this set, duplicate input values for sizes so we have matching index
insert #Input values ('Panel', 1, '20.50') -- x value
insert #Input values ('Panel', 2, '20.50') -- x value, same as prior index
insert #Input values ('Panel', 3, '564.75') --y value
insert #Input values ('Panel', 4, '564.75') --y value, same as prior index
insert #Input values ('Panel', 5, '6.0') -- z value
insert #Input values ('Panel', 6, '6.0') -- z value, same as prior index
insert #Input values ('Panel', 10, 'c1') -- color specied
insert #Input values ('Panel', 14, 'M1') -- mat
insert #Input values ('Back', 1, '20.50') -- x value
insert #Input values ('Back', 2, '20.50') -- x value, same as prior index
insert #Input values ('Back', 3, '564.75') --y value
insert #Input values ('Back', 4, '564.75') --y value, same as prior index
insert #Input values ('Back', 5, '6.0') -- z value
insert #Input values ('Back', 6, '6.0') -- z value, same as prior index
insert #Input values ('Back', 10, 'a1') -- color specied
insert #Input values ('Back', 14, 'M1') -- mat
--insert #Input values ('Panel', 15, 'a') -- mat
-- Now, let us try to match
--set nocount off
-- multirecord match
SELECT v.ItemFamily, v.ItemMatch
FROM
(
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c
JOIN #Input i
ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID
AND case when i.PropertyID = 1 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 2 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 3 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 4 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 5 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 6 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID > 6 then c.CriteriaValue else i.CriteriaValue end
= i.CriteriaValue
GROUP BY c.ItemFamily, c.ItemMatch
)v
JOIN
(
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
"Farmer" <someone@.somewhere.com> wrote in message
news:ecWGFEyEGHA.376@.TK2MSFTNGP12.phx.gbl...
> /*
> Please help.
> Definition:
> I am trying to compose a query that will do part matching based on certain
> input criteria, by item class.
> I want to define a table called criteria and let users define sets of
> criteria that may satisfy a part match. if I have
> an input set of parameters, i want to match this set to all defined
> criteria sets. The first six define size matching, via range.
> It is assumend that indexes of 1-2 is x size range, 3-4 is y size range
> and 5-6 is z size range. Therefore the first part is to ensure that
> any xyz size part by class is to ensure that it fits XYZ specified
> criteria ranges for that class.
> I got that working below. Any parameter (index > 6) has to match exactly,
> no ranges. Like if a part has material M1, matching set(s) have to have
> that
> material as well, = input material.
> The second requirement is that an input criteria must find record sets
> that matches defined input exactly or can have extra elements in criteria,
> that can be ignored, as long as it is not specified in input set.
> I do understand that this is a case of set relational division.
> I am trying to find sets that contain a specific subset.
> If I get multiple sets matching, the top ranked (defined later)
> ResultingItem (set) will be used as the final part match.
> This seems to be the difficult part, escaping me.
> Please help. Your effort and time is greatly appreciated.
> farmer
> In the example below, input set matches criteria for ItemA, not ItemX,
> therefore ItemA is the match.
> If input record index 10 would not exists, then both would match.
> Appropriate characteristics have the same index (7 to N)
> */
> set nocount on
> declare @.criteria table
> (
> ItemClass varchar(10) not null
> ,indID int
> ,indValue varchar(50)
> ,ResultingItem varchar(10) not null
> ,primary key (ItemClass, indID, ResultingItem)
> )
> declare @.Input table
> (
> ItemClass varchar(10)
> ,indID int
> ,indValue varchar(50)
> )
> -- this is defined criteria
> insert @.criteria values ('Panel', 1, '20', 'ItemA') -- x from
> insert @.criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
> insert @.criteria values ('Panel', 3, '564.75', 'ItemA') --y from
> insert @.criteria values ('Panel', 4, '564.75', 'ItemA') --y to
> insert @.criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
> insert @.criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
> insert @.criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined
> but IMOS data will not have it
> insert @.criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
> insert @.criteria values ('Panel', 15, 'a', 'ItemA') -- other
> characteristic
> insert @.criteria values ('Panel', 1, '20', 'ItemX') -- x from
> insert @.criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
> insert @.criteria values ('Panel', 3, '564.75', 'ItemX') --y from
> insert @.criteria values ('Panel', 4, '564.75', 'ItemX') --y to
> insert @.criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
> insert @.criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
> insert @.criteria values ('Panel', 10, 'A1', 'ItemX') -- color -- defined
> but IMOS data will not have it
> insert @.criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
> insert @.criteria values ('Panel', 15, 'c', 'ItemX') -- other
> characteristic
> -- in this set, duplicate input values for sizes so we have matching index
> insert @.Input values ('Panel', 1, '20.5') -- x value
> insert @.Input values ('Panel', 2, '20.5') -- x value, same as prior index
> insert @.Input values ('Panel', 3, '564.75') --y value
> insert @.Input values ('Panel', 4, '564.75') --y value, same as prior index
> insert @.Input values ('Panel', 5, '6.0') -- z value
> insert @.Input values ('Panel', 6, '6.0') -- z value, same as prior index
> insert @.Input values ('Panel', 10, 'c1') -- color specied
> insert @.Input values ('Panel', 14, 'M1') -- mat
> -- Now, let us try to match
> set nocount off
> select c.ItemClass, c.ResultingItem, COUNT(*)
> FROM @.criteria c
> LEFT JOIN @.Input i
> on c.ItemClass = i.ItemClass
> AND c.indID = i.indID
> AND (case when i.indID = 1 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 2 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID > 6 then c.indValue else i.indValue end) =
> i.indValue
> GROUP BY c.ItemClass, c.ResultingItem--, c.ItemClass
> --HAVING COUNT(*) <=
> --AND COUNT(c.ItemClass) =
> --(
> -- SELECT COUNT(c2.ItemClass)
> -- FROM @.criteria c2
> -- WHERE c2.ItemClass = i.ItemClass
> -- GROUP BY c2.ItemClass
> --)
>
>
> select *
> from @.Input i
> LEFT JOIN @.criteria c
> --on c.ItemClass = i.ItemClass AND c.indID = i.indID
> on c.ItemClass = i.ItemClass AND c.indID = i.indID
> AND (case when i.indID = 1 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 2 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID > 6 then c.indValue else i.indValue end) =
> i.indValue
>
>
> --SELECT PS1.pilot
> --FROM PilotSkills AS PS1
> --JOIN Hangar AS H1 ON PS1.plane = H1.plane
> --GROUP BY PS1.pilot
> --HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar);
> --
> --print 'Fourth way'
> --SELECT PS1.pilot
> --FROM PilotSkills AS PS1
> --LEFT OUTER JOIN Hangar AS H1 ON PS1.plane = H1.plane
> --GROUP BY PS1.pilot
> --HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
> --AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar);
>
>|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Improved solution
if object_id('tempdb..#criteria') is not null
drop table #criteria
go
if object_id('tempdb..#Input') is not null
drop table #Input
go
CREATE TABLE #Criteria
(
ItemFamily varchar(10) not null
,PropertyID int
,CriteriaValue varchar(50)
,ItemMatch varchar(10) not null
,PRIMARY KEY CLUSTERED (PropertyID, ItemFamily, ItemMatch)
)
CREATE TABLE #Input
(
ItemFamily varchar(10)
,PropertyID int
,CriteriaValue varchar(50)
,PRIMARY KEY CLUSTERED(PropertyID, ItemFamily)
)
go
set nocount on
-- this is defined criteria
insert #Criteria values ('Panel', 1, '20.50', 'ItemA') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemA') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemA') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
insert #Criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined but
IMOS data will not have it
--insert #Criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
insert #Criteria values ('Panel', 15, 'a', 'ItemA') -- other characteristic
insert #Criteria values ('Panel', 1, '20.00', 'ItemX') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemX') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemX') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
insert #Criteria values ('Panel', 10, 'c1', 'ItemX') -- color
insert #Criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
insert #Criteria values ('Panel', 15, 'c', 'ItemX') -- other characteristic
insert #Criteria values ('Back', 1, '20.50', 'ItemZ') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemZ') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemZ') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemZ') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemZ') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemZ') -- z to
insert #Criteria values ('Back', 10, 'a1', 'ItemZ') -- color
insert #Criteria values ('Back', 14, 'M1', 'ItemZ') -- mat
insert #Criteria values ('Back', 15, 'a', 'ItemZ') -- other characteristic
insert #Criteria values ('Back', 1, '20.00', 'ItemY') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemY') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemY') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemY') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemY') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemY') -- z to
insert #Criteria values ('Back', 10, 'a1', 'ItemY') -- color
insert #Criteria values ('Back', 14, 'M1', 'ItemY') -- mat
insert #Criteria values ('Back', 15, 'c', 'ItemY') -- other characteristic
-- in this set, duplicate input values for sizes so we have matching index
insert #Input values ('Panel', 1, '20.50') -- x value
insert #Input values ('Panel', 2, '20.50') -- x value, same as prior index
insert #Input values ('Panel', 3, '564.75') --y value
insert #Input values ('Panel', 4, '564.75') --y value, same as prior index
insert #Input values ('Panel', 5, '6.0') -- z value
insert #Input values ('Panel', 6, '6.0') -- z value, same as prior index
insert #Input values ('Panel', 10, 'c1') -- color specied
insert #Input values ('Panel', 14, 'M1') -- mat
insert #Input values ('Back', 1, '20.50') -- x value
insert #Input values ('Back', 2, '20.50') -- x value, same as prior index
insert #Input values ('Back', 3, '564.75') --y value
insert #Input values ('Back', 4, '564.75') --y value, same as prior index
insert #Input values ('Back', 5, '6.0') -- z value
insert #Input values ('Back', 6, '6.0') -- z value, same as prior index
insert #Input values ('Back', 10, 'a1') -- color specied
insert #Input values ('Back', 14, 'M1') -- mat
--insert #Input values ('Panel', 15, 'a') -- mat
-- Now, let us try to match
--set nocount off
-- Multi family record match
SELECT c.ItemFamily, c.ItemMatch--, COUNT(*) as Cnt
FROM #Criteria c
JOIN #Input i
ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID
AND case when i.PropertyID = 1 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 2 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 3 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 4 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 5 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 6 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID > 6 then c.CriteriaValue else i.CriteriaValue end
= i.CriteriaValue
GROUP BY c.ItemFamily, c.ItemMatch
HAVING COUNT(*) =
(
SELECT COUNT(*) as Cnt
FROM #Input i2
WHERE i2.ItemFamily = c.ItemFamily
GROUP BY i2.ItemFamily
)
"Farmer" <someone@.somewhere.com> wrote in message
news:esqym97EGHA.3348@.TK2MSFTNGP10.phx.gbl...
> thank you all
> I did come up with an answer. Any optimizations you see?
> if object_id('tempdb..#criteria') is not null
> drop table #criteria
> go
> if object_id('tempdb..#Input') is not null
> drop table #Input
> go
> CREATE TABLE #Criteria
> (
> ItemFamily varchar(10) not null
> ,PropertyID int
> ,CriteriaValue varchar(50)
> ,ItemMatch varchar(10) not null
> ,PRIMARY KEY CLUSTERED (PropertyID, ItemFamily, ItemMatch)
> )
> CREATE TABLE #Input
> (
> ItemFamily varchar(10)
> ,PropertyID int
> ,CriteriaValue varchar(50)
> ,PRIMARY KEY CLUSTERED(PropertyID, ItemFamily)
> )
> go
> set nocount on
> -- this is defined criteria
> insert #Criteria values ('Panel', 1, '20.50', 'ItemA') -- x from
> insert #Criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
> insert #Criteria values ('Panel', 3, '564.75', 'ItemA') --y from
> insert #Criteria values ('Panel', 4, '564.75', 'ItemA') --y to
> insert #Criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
> insert #Criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
> insert #Criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
> insert #Criteria values ('Panel', 15, 'a', 'ItemA') -- other
> characteristic
> insert #Criteria values ('Panel', 1, '20.00', 'ItemX') -- x from
> insert #Criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
> insert #Criteria values ('Panel', 3, '564.75', 'ItemX') --y from
> insert #Criteria values ('Panel', 4, '564.75', 'ItemX') --y to
> insert #Criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
> insert #Criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
> insert #Criteria values ('Panel', 10, 'a1', 'ItemX') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
> insert #Criteria values ('Panel', 15, 'c', 'ItemX') -- other
> characteristic
>
> insert #Criteria values ('Back', 1, '20.50', 'ItemZ') -- x from
> insert #Criteria values ('Back', 2, '20.75', 'ItemZ') -- x to
> insert #Criteria values ('Back', 3, '564.75', 'ItemZ') --y from
> insert #Criteria values ('Back', 4, '564.75', 'ItemZ') --y to
> insert #Criteria values ('Back', 5, '5.75', 'ItemZ') -- z from
> insert #Criteria values ('Back', 6, '6.75', 'ItemZ') -- z to
> insert #Criteria values ('Back', 10, 'c1', 'ItemZ') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Back', 14, 'M1', 'ItemZ') -- mat
> insert #Criteria values ('Back', 15, 'a', 'ItemZ') -- other characteristic
> insert #Criteria values ('Back', 1, '20.00', 'ItemY') -- x from
> insert #Criteria values ('Back', 2, '20.75', 'ItemY') -- x to
> insert #Criteria values ('Back', 3, '564.75', 'ItemY') --y from
> insert #Criteria values ('Back', 4, '564.75', 'ItemY') --y to
> insert #Criteria values ('Back', 5, '5.75', 'ItemY') -- z from
> insert #Criteria values ('Back', 6, '6.75', 'ItemY') -- z to
> insert #Criteria values ('Back', 10, 'a1', 'ItemY') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Back', 14, 'M1', 'ItemY') -- mat
> insert #Criteria values ('Back', 15, 'c', 'ItemY') -- other characteristic
>
> -- in this set, duplicate input values for sizes so we have matching index
> insert #Input values ('Panel', 1, '20.50') -- x value
> insert #Input values ('Panel', 2, '20.50') -- x value, same as prior index
> insert #Input values ('Panel', 3, '564.75') --y value
> insert #Input values ('Panel', 4, '564.75') --y value, same as prior index
> insert #Input values ('Panel', 5, '6.0') -- z value
> insert #Input values ('Panel', 6, '6.0') -- z value, same as prior index
> insert #Input values ('Panel', 10, 'c1') -- color specied
> insert #Input values ('Panel', 14, 'M1') -- mat
> insert #Input values ('Back', 1, '20.50') -- x value
> insert #Input values ('Back', 2, '20.50') -- x value, same as prior index
> insert #Input values ('Back', 3, '564.75') --y value
> insert #Input values ('Back', 4, '564.75') --y value, same as prior index
> insert #Input values ('Back', 5, '6.0') -- z value
> insert #Input values ('Back', 6, '6.0') -- z value, same as prior index
> insert #Input values ('Back', 10, 'a1') -- color specied
> insert #Input values ('Back', 14, 'M1') -- mat
> --insert #Input values ('Panel', 15, 'a') -- mat
> -- Now, let us try to match
> --set nocount off
> -- multirecord match
> SELECT v.ItemFamily, v.ItemMatch
> FROM
> (
> SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
> FROM #Criteria c
> JOIN #Input i
> ON c.ItemFamily = i.ItemFamily
> AND c.PropertyID = i.PropertyID
> AND case when i.PropertyID = 1 then c.CriteriaValue else i.CriteriaValue
> end <= i.CriteriaValue
> AND case when i.PropertyID = 2 then c.CriteriaValue else i.CriteriaValue
> end
> AND case when i.PropertyID = 3 then c.CriteriaValue else i.CriteriaValue
> end <= i.CriteriaValue
> AND case when i.PropertyID = 4 then c.CriteriaValue else i.CriteriaValue
> end
> AND case when i.PropertyID = 5 then c.CriteriaValue else i.CriteriaValue
> end <= i.CriteriaValue
> AND case when i.PropertyID = 6 then c.CriteriaValue else i.CriteriaValue
> end
> AND case when i.PropertyID > 6 then c.CriteriaValue else i.CriteriaValue
> end = i.CriteriaValue
> GROUP BY c.ItemFamily, c.ItemMatch
> )v
> JOIN
> (
> SELECT ItemFamily, COUNT(*) as Cnt
> FROM #Input i
> GROUP BY i.ItemFamily
> ) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
>
>
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:ecWGFEyEGHA.376@.TK2MSFTNGP12.phx.gbl...
>|||thank you for your reply.
In my design, I try to "normalize" this type of schema.
criteria table
keyID, class, col1, col2,col3,col4, ItemMatchID
select *
from criteria
where class= @.someclassvalue and col1=@.someproperty1 AND
col2=@.someproperty12 and on to narrow or match finite ItemMatchID.
this gets complicated when the size as a property is required to support
ranges. this range (for now) in my design requires duplication so that two
edges can be compared in a record set, fromrange vs value vs torange.
Turning the above mentioned table into "tower" record set, makes it flexible
as there is no limit as to how many properties can be defined. and the
answer for matches can be answered via relational division, ie a fixed list
of properties must be contained in the criteria set.
if range would not be requred, then pure case of relational division can be
used. due to range requirement, I had to imprivise in my own way, that I had
published. Criteria is a fixed permanent table and Input will be temp table
in stored procedure.
Any better ideas?
I sent better solution in another post to relational division problem. All
this answeres my problem well.
thanks
farmer
"Razvan Socol" <RazvanSocol@.discussions.microsoft.com> wrote in message
news:5CFF4D42-2FD2-4FFC-9A45-E5CB431213F3@.microsoft.com...
> Hi, Farmer
> I think the following query is equivalent to your query:
> SELECT v.ItemFamily, v.ItemMatch
> FROM (
> SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
> FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
> AND c.PropertyID = i.PropertyID AND (
> i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
> OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
> OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
> )
> GROUP BY c.ItemFamily, c.ItemMatch
> ) v JOIN (
> SELECT ItemFamily, COUNT(*) as Cnt
> FROM #Input i
> GROUP BY i.ItemFamily
> ) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
> However, if the #Input is really a table (not a view), I don't like the
> idea of duplicating the values in the #Input table, just so you have a
> matching index. You should consider putting more meaning into the
> database: those comments that you have next to each row in the sample
> data... they should be in some table. I don't know the specifics of
> your database (and I don't think I want to know all of them), but you
> should give it another thought.
> Razvan
>|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan

help with one more SQL query...

i am trying to figure out how to write one more sql query. basically i now have 2 tables, both filled with item numbers and quantities. i want to write queries that will produce what is missing between the two tables. here is what i need more specifically:

a query that looks for item numbers that are in one table and not the other, and vice versa (ie item number 20004 is in our table, but doesn't exist in the other table).

a query that prints out discrepancies between quantities for item numbers that do match up (ie item number 20004 is in both tables, but has a quantity of 10 in one and 20 in the other).

it seems that i should be using the SQL join functions for these? is that right, or is there a better way to do this??yes, for the first task (rows in one table but not the other) you will need two queries, each featuring a LEFT OUTER JOIN, with a test for IS NULL in the WHERE clauseselect table1.itemnumber
from table1
left outer
join table2
on table1.itemnumber
= table2.itemnumber
where table2.itemnumber is null

select table2.itemnumber
from table2
left outer
join table1
on table2.itemnumber
= table1.itemnumber
where table1.itemnumber is null

for the second task you will need a simple INNER JOINselect table1.itemnumber
, table1.quantity
, table2.quantity
from table1
inner
join table2
on table1.itemnumber
= table2.itemnumber
where table1.quantity
<> table2.quantity|||Essentially the queryselect table1.itemnumber
from table1 left outer join table2 on table1.itemnumber = table2.itemnumber
where table2.itemnumber is nullis a "set difference", hence can also be achieved by using an "EXCEPT" construction:select itemnumber from table1
EXCEPT ALL
select itemnumber from table2The main difference in the result being that in the former case, duplicates in table1 will be shown (as duplicates) only if they aren't present in table2, while in the latter case duplicates may be shown (but with a smaller repeat count) when they are present in the second table.
Without duplicates in table1, both queries give the same result.
In most situations, the EXCEPT query will be faster, though, especially if the second table is large.|||In most situations, the EXCEPT query will be faster...unless your database system doesn't support that syntax, in which case it will take positively forever ;) :)|||unless your database system doesn't support that syntaxin which case it will probably neither support the OUTER JOIN syntax ...
;)|||um, peter, they all support OUTER JOIN syntax ;)

which database(s) were you thinking of that do support EXCEPT?|||Just thinking of Oracle (up to version 9) which has no FULL OUTER JOIN, but has MINUS.
Maybe there are others as well, no idea.|||but you don't need FULL OUTER JOIN to create an "EXCEPT" query, just LEFT OUTER JOIN|||And which DB systems were you thinking of that do not support EXCEPT?|||many more than you were!! ;) :)|||That would (not) surprise me!
;)|||I call a remote/stored procedure on another server.

It then calls other stored procedure with the EXEC statement.

It seems to me that logically this would get done remotely as well no? But It seems not to be from my tests.

Is there a easy way to specify that the SP - and all it's sub calls get done remotely, or do I need to specifically specify for each of these that they should be run remotely in each and every EXEC statement?

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.