Join from highest values in a different table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join from highest values in a different table

Author

Topic

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 04:27:39

Hi all,

I am trying to put a SELECT statement together using two tables but I am finding it rather complicated, hope someone can help.

I have two tables 'Items' and 'Transactions'

My 'Items' table records the live stock levels and my 'Transactions' holds the transaction details whether it be an issue, return or restock.

What i am trying to generate is a statement that checks my 'Items' table, compares the current qty with the maximum (these two fields are held in the 'Items') and if it less than maximum output the values but also match it against the issue in my transactions.

In my 'Transactions' table I have many columns including 'OPID' which in incremental.

So for example if in my Items table their is one item below Maximum e.g. 'Wheel Nut'. I want a select statement that outputs

'Wheel Nut' and the last Issue OPERATIONID. This is because i need to see who issued it against a restock.

I know this sounds complicated but if anyone needs anymore information to help me please ask.

Sam

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-12 : 04:30:14

will u put some sample data and expected output?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 04:47:34

Hi,

'Transactions' table

OPID ISSUETYPE Item User
1 Issue WheelNut Sam
2 Issue WheelNut James
3 Return Cage Sam
4 Restock Bolt Jamie
5 Issue WheelNut Sam

My 'Items' table

Item CURRENTQTY QTYMAX

WheelNut 1 3
Cage 3 3
Bolt 3 3

Out of this i would expect to see my statement

WheelNut James
WheelNut Sam

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-12 : 05:16:49

select s.item,t.user from transactions t
inner join (select item,max(currentqty) as currentqty from items group by item)s on s.item = t.item
inner join items i on i.item = s.item and s.currentqty < i.qtymax

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 06:27:09

I get an error saying Multi-part Indentifier "s.currentqty" could not be bound.

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 06:34:52

Sorry, it does work except it displayed three results
it shows the Sam Return transaction

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 06:37:59

So far SQL;

SELECT     s.item, t.usercode
FROM         dbo.transactions AS t INNER JOIN
                          (SELECT     item, MAX(currentqty) AS currentqty
                            FROM          dbo.items
                            GROUP BY item) AS s ON s.item = t.item INNER JOIN
                      dbo.items AS i ON i.item = s.item AND s.currentqty < i.qtymax

Except this just shows all the issues (3 in total) when it should only show the latested two OPID 2 and 5.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-12 : 07:04:24

use distint keyword or
select item,usercode
from (
SELECT row_number()over(partiton by s.item order by opid desc)as rid s.item, t.usercode
FROM dbo.transactions AS t
INNER JOIN
(SELECT item, MAX(currentqty) AS currentqty
FROM dbo.items
GROUP BY item) AS s ON s.item = t.item INNER JOIN
dbo.items AS i ON i.item = s.item AND s.currentqty < i.qtymax)s where s.rid = 1

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 07:36:22

Now the issue is with;

The OVER SQL construct or statement is not supported..

And incorrect syntax near partition.

Sam

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-12 : 07:54:53

quote:
Originally posted by bklr

use distint keyword or
select item,usercode
from (
SELECT row_number()over(partition by s.item order by opid desc)as rid s.item, t.usercode
FROM dbo.transactions AS t
INNER JOIN
(SELECT item, MAX(currentqty) AS currentqty
FROM dbo.items
GROUP BY item) AS s ON s.item = t.item INNER JOIN
dbo.items AS i ON i.item = s.item AND s.currentqty < i.qtymax)s where s.rid = 1

]

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 08:00:13

Nearly!

Incorrect syntax near 's'.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-12 : 08:09:15

quote:
Originally posted by samtwilliams

Nearly!

Incorrect syntax near 's'.

select item,usercode
from (
SELECT row_number()over(partition by s.item order by opid desc)as rid, s.item, t.usercode
FROM dbo.transactions AS t
INNER JOIN
(SELECT item, MAX(currentqty) AS currentqty
FROM dbo.items
GROUP BY item) AS s ON s.item = t.item INNER JOIN
dbo.items AS i ON i.item = s.item AND s.currentqty < i.qtymax)s where s.rid = 1

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 08:18:37

The result shows;

WheelNut Sam

Expected;

WheelNut James
WheelNut Sam

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-12 : 08:19:37

select item,usercode
from (
SELECT row_number()over(partition by s.item,s.user order by opid desc)as rid, s.item, t.usercode
FROM dbo.transactions AS t
INNER JOIN
(SELECT item, MAX(currentqty) AS currentqty
FROM dbo.items
GROUP BY item) AS s ON s.item = t.item INNER JOIN
dbo.items AS i ON i.item = s.item AND s.currentqty < i.qtymax)s where s.rid = 1

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 08:26:30

quote:
SELECT item, usercode
FROM (SELECT row_number() OVER (partition BY s.item, s. usercode
ORDER BY opid DESC) AS rid, s.item, t .usercode
FROM dbo.transactions AS t INNER JOIN
(SELECT item, MAX(currentqty) AS currentqty
FROM dbo.items
GROUP BY item) AS s ON s.item = t .item INNER JOIN
dbo.items AS i ON i.item = s.item AND s.currentqty < i.qtymax) s
WHERE s.rid = 1

I have modified USER to user code as it was being picked up as an error but now it states;

Invalid column name usercode

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 08:27:36

t.user code seems to have fixed it

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 08:31:15

Now if i manipulate the table data so that

currentqty = 2, i expect to see;

WheelNut Sam

but what actually shows is;
WheelNut James
WheelNut Sam

samtwilliams
Starting Member

18 Posts

Posted - 2009-08-12 : 10:11:35

Still no look, stil obtaining results i don't expect.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-12 : 13:08:11

[code]DECLARE @Transactions TABLE (OPID INT, ISSUETYPE VARCHAR(50), Item VARCHAR(50), [User] VARCHAR(50))

INSERT @Transactions
SELECT 1, 'Issue', 'WheelNut', 'Sam'
UNION ALL SELECT 2, 'Issue', 'WheelNut', 'James'
UNION ALL SELECT 3, 'Return', 'Cage', 'Sam'
UNION ALL SELECT 4, 'Restock', 'Bolt', 'Jamie'
UNION ALL SELECT 5, 'Issue', 'WheelNut', 'Sam'

DECLARE @Items TABLE (Item VARCHAR(50), CURRENTQTY INT, QTYMAX INT)

INSERT @Items
SELECT 'WheelNut', 1, 3
UNION ALL SELECT 'Cage', 3, 3
UNION ALL SELECT 'Bolt', 3, 3

SELECT *
FROM
(
SELECT
T.Item, T.[User],
ROW_NUMBER() OVER (PARTITION BY T.Item, T.[User] ORDER BY T.Item, T.OPID DESC) AS RowNum
FROM
@Transactions AS T
INNER JOIN
(
SELECT Item, MAX(currentqty) AS CurrentQty
FROM @Items
WHERE CurrentQty < QtyMax
GROUP BY item
) AS I
ON T.Item = I.Item
WHERE
T.IssueType = 'Issue'

) AS D
WHERE RowNum = 1

-- Or without RowNumber

SELECT
T.Item,
T.[User]
FROM
@Transactions AS T
INNER JOIN
(
SELECT Item, MAX(currentqty) AS CurrentQty
FROM @Items
WHERE CurrentQty < QtyMax
GROUP BY item
) AS I
ON T.Item = I.Item
WHERE
T.IssueType = 'Issue'
GROUP BY
T.Item,
T.[User]
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources