Question - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Question

Author

Topic

dhenrysql
Starting Member

6 Posts

Posted - 2010-06-21 : 10:32:59

I have 4 Rows

Bin#
QTY
ITEM #
BinSequence (Either 1 or 2) (Primary=1 or Secondary=2)

Does anyone know how I could write a query and only include Item Numbers that would have multiple Secondary Locations?


BinNumber    Qty    ItemNumber    BinSeq
53384    1    ZUK90002                1
22245    0    ZUK90002                2
23721    2    ZUK80035                1
21592    31    ZUK66137                1
21591    40    ZUK66135                1
21592    22    ZUK66132                1
23722    25    ZUK44057                1
23701    43    ZUK44051                1
23702    39    ZUK44050                1
23702    15    ZUK33054                1
23703    204    ZUK33054                2
27103    0    ZUK33054                2
23623    0    ZUK33054                2
23613    0    ZUK33054                2

So from this example the result would be


BinNumber    Qty    ItemNumber    BinSeq
23702    15    ZUK33054                1
23703    204    ZUK33054                2
27103    0    ZUK33054                2
23623    0    ZUK33054                2
23613    0    ZUK33054                2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-21 : 10:39:21

[code]
select *
from yourtable s
where exists
(
select ItemNumber
from yourtable x
where x.ItemNumber = s.ItemNumber
and BinSeq = 2
group by ItemNumber
having count(*) >= 2
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dhenrysql
Starting Member

6 Posts

Posted - 2010-06-21 : 12:11:52

SELECT WRHS_Item.ItemNumber,WRHS_Bin.BinNumber, WRHS_BinItem.BinSeq, WRHS_BinItemQty.Qty, WRHS_Item.ItemDescription
FROM WRHS_Bin INNER JOIN
WRHS_BinItem ON WRHS_Bin.BinID = WRHS_BinItem.BinID INNER JOIN
WRHS_BinItemQty ON WRHS_BinItem.BinItemID = WRHS_BinItemQty.BinItemID INNER JOIN
WRHS_Item ON WRHS_BinItem.ItemID = WRHS_Item.ItemID
WHERE WRHS_BinItemQty.QTY > 0 AND exists
(
select ItemNumber
from WRHS_Item x
where x.ItemNumber = s.ItemNumber
and WRHS_BinItem.BinSeq = 2
group by WRHS_Item.ItemNumber
having count(*) >= 2
)

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "s.ItemNumber" could not be bound.

I am getting this error. I am new to SQL and appreciate any help you can provide.

Thanks

dhenrysql
Starting Member

6 Posts

Posted - 2010-06-21 : 12:17:49

pk bohra

Msg 164, Level 15, State 1, Line 1
Each GROUP BY expression must contain at least one column that is not an outer reference.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-21 : 12:19:29

I realised that the changes i did for sql are not perfect.
I deleted my post but in the mean time you have posted reply.
Don't mind.

quote:
Originally posted by dhenrysql

pk bohra

Msg 164, Level 15, State 1, Line 1
Each GROUP BY expression must contain at least one column that is not an outer reference.

I am here to learn from Masters and help new bees in learning.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-21 : 12:22:49

Try this:

SELECT WRHS_Item.ItemNumber,WRHS_Bin.BinNumber, WRHS_BinItem.BinSeq,
WRHS_BinItemQty.Qty, WRHS_Item.ItemDescription
FROM WRHS_Bin INNER JOIN
WRHS_BinItem ON WRHS_Bin.BinID = WRHS_BinItem.BinID INNER JOIN
WRHS_BinItemQty ON WRHS_BinItem.BinItemID = WRHS_BinItemQty.BinItemID INNER JOIN
WRHS_Item s ON WRHS_BinItem.ItemID = WRHS_Item.ItemID
WHERE WRHS_BinItemQty.QTY > 0 and WRHS_BinItem.BinSeq = 2 AND exists
(
select ItemNumber
from WRHS_Item x
where x.ItemNumber = s.ItemNumber
group by x.ItemNumber
having count(*) >= 2
)

In case the solution is not correct then please give the structure of all the tables used.

dhenrysql
Starting Member

6 Posts

Posted - 2010-06-21 : 15:24:06

Sorry did not see you updated your reply.

SELECT WRHS_Item.ItemNumber,WRHS_Bin.BinNumber, WRHS_BinItem.BinSeq,
WRHS_BinItemQty.Qty, WRHS_Item.ItemDescription
FROM WRHS_Bin INNER JOIN
WRHS_BinItem ON WRHS_Bin.BinID = WRHS_BinItem.BinID INNER JOIN
WRHS_BinItemQty ON WRHS_BinItem.BinItemID = WRHS_BinItemQty.BinItemID INNER JOIN
WRHS_Item s ON WRHS_BinItem.ItemID = WRHS_Item.ItemID
WHERE WRHS_BinItemQty.QTY > 0 and WRHS_BinItem.BinSeq = 2 AND exists
(
select ItemNumber
from WRHS_Item x
where x.ItemNumber = s.ItemNumber
group by x.ItemNumber
having count(*) >= 2
)

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "WRHS_Item.ItemID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "WRHS_Item.ItemNumber" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "WRHS_Item.ItemDescription" could not be bound.

WRHS_Item.ItemNumber (varchar(50), not null)
WRHS_Bin.BinNumber (varchar(10), not null
WRHS_BinItem.BinSeq (varchar(10), not null)
WRHS_BinItemQty.Qty (int, not null)
WRHS_Item.ItemDescription (varchar(75), not null)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-21 : 16:14:00

Did you try Tan's solution posted at "06/21/2010 : 10:39:21"

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-21 : 23:54:59

Try this:

SELECT WRHS_Bin.BinNumber,WRHS_BinItemQty.Qty, s.ItemNumber, WRHS_BinItem.BinSeq,
s.ItemDescription
FROM WRHS_Bin INNER JOIN
WRHS_BinItem ON WRHS_Bin.BinID = WRHS_BinItem.BinID INNER JOIN
WRHS_BinItemQty ON WRHS_BinItem.BinItemID = WRHS_BinItemQty.BinItemID INNER JOIN
WRHS_Item s ON WRHS_BinItem.ItemID = s.ItemID
WHERE exists
(
select ItemNumber
from WRHS_Item x join WRHS_BinItem y
on x.itemid = y.itemid and y.binseq = 2
where x.ItemNumber = s.ItemNumber
group by x.ItemNumber
having count(*) >= 2
)

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-22 : 00:04:04

in my original query, the "s" and the x is the table alias given to the table.

I have amended your query .. Give this a try

quote:

Originally posted by dhenrysql

SELECT      WRHS_Item.ItemNumber,WRHS_Bin.BinNumber, WRHS_BinItem.BinSeq, WRHS_BinItemQty.Qty, WRHS_Item.ItemDescription
FROM         WRHS_Bin INNER JOIN
                      WRHS_BinItem ON WRHS_Bin.BinID = WRHS_BinItem.BinID INNER JOIN
                      WRHS_BinItemQty ON WRHS_BinItem.BinItemID = WRHS_BinItemQty.BinItemID INNER JOIN
                      WRHS_Item ON WRHS_BinItem.ItemID = WRHS_Item.ItemID
WHERE WRHS_BinItemQty.QTY > 0  AND  exists
(
		select	x.ItemNumber
		from	WRHS_Item x
		where	x.ItemNumber	= WRHS_Item.ItemNumber
		and	x.BinSeq	= 2
		group by x.ItemNumber
		having count(*) >= 2
	)

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "s.ItemNumber" could not be bound.

I am getting this error. I am new to SQL and appreciate any help you can provide.

Thanks

KH
[spoiler]Time is always against us[/spoiler]

dhenrysql
Starting Member

6 Posts

Posted - 2010-06-22 : 08:38:32

Really appreciate your help!

If I run this I get 16,000 rows

SELECT WRHS_Item.ItemNumber,WRHS_Bin.BinNumber, WRHS_BinItem.BinSeq, WRHS_BinItemQty.Qty, WRHS_Item.ItemDescription
FROM WRHS_Bin INNER JOIN
WRHS_BinItem ON WRHS_Bin.BinID = WRHS_BinItem.BinID INNER JOIN
WRHS_BinItemQty ON WRHS_BinItem.BinItemID = WRHS_BinItemQty.BinItemID INNER JOIN
WRHS_Item ON WRHS_BinItem.ItemID = WRHS_Item.ItemID
WHERE WRHS_BinItemQty.QTY > 0

If I run this

SELECT WRHS_Item.ItemNumber,WRHS_Bin.BinNumber, WRHS_BinItem.BinSeq, WRHS_BinItemQty.Qty, WRHS_Item.ItemDescription
FROM WRHS_Bin INNER JOIN
WRHS_BinItem ON WRHS_Bin.BinID = WRHS_BinItem.BinID INNER JOIN
WRHS_BinItemQty ON WRHS_BinItem.BinItemID = WRHS_BinItemQty.BinItemID INNER JOIN
WRHS_Item ON WRHS_BinItem.ItemID = WRHS_Item.ItemID
WHERE WRHS_BinItemQty.QTY > 0 AND exists
(
select WRHS_Item.ItemNumber
from WRHS_Item x
where x.ItemNumber = WRHS_Item.ItemNumber
and WRHS_BinItem.BinSeq = 2
group by x.ItemNumber
having count(*) >= 2
)

I get 0 rows.

I appreciate your help. I think for me it will be easier to just push it out to excel.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-22 : 09:08:15

try with following . . . note the line in red


exists
(
	select	x.ItemNumber
	from	WRHS_Item x
	where	x.ItemNumber	= WRHS_Item.ItemNumber
	and	x.BinSeq	= 2
	group by x.ItemNumber
	having count(*) >= 2
)

KH
[spoiler]Time is always against us[/spoiler]

dhenrysql
Starting Member

6 Posts

Posted - 2010-06-22 : 12:08:35

Msg 207, Level 16, State 1, Line 12
Invalid column name 'BinSeq'.

I get this error.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-22 : 12:12:15

Did you try the solution i posted above:

For your ready reference:

SELECT WRHS_Bin.BinNumber,WRHS_BinItemQty.Qty, s.ItemNumber, WRHS_BinItem.BinSeq,
s.ItemDescription
FROM WRHS_Bin INNER JOIN
WRHS_BinItem ON WRHS_Bin.BinID = WRHS_BinItem.BinID INNER JOIN
WRHS_BinItemQty ON WRHS_BinItem.BinItemID = WRHS_BinItemQty.BinItemID INNER JOIN
WRHS_Item s ON WRHS_BinItem.ItemID = s.ItemID
WHERE exists
(
select ItemNumber
from WRHS_Item x join WRHS_BinItem y
on x.itemid = y.itemid and y.binseq = 2
where x.ItemNumber = s.ItemNumber
group by x.ItemNumber
having count(*) >= 2
)

Try and let us know if you have any issues.
Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources