Probably a simple one for you SQL gurus! 1

[lyontl]

I have a table with sales order numbers and for each part on the sales order there is a seperate entry.

i.e. if there are three parts sold on sales order 123, there is an entry for each part number, totalling three entries with the sales order value of 123

I need to pull all of the entries for all of the sales orders that have a particular part number. So if Sales Order 123 has a part B, then I need to pull all three entries for sales order 123. However, if sales order 456 has four entries and none of them are of the value B, then they need to be excluded.

Make sense? I hope you can help, thanks in advance!

 

[mrdenny]

I'm assuming that you are using Microsoft SQL Server?

Simple subquery in the join will do the trick.

select a.*
from table a
join (select OrderID from table where PartNumber = 'B') b on a.OrderID = b.OrderID

Can also be written this way.

select *
from table
where OrderID in (select OrderID from table where PartNumber = 'B')

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[lyontl]

Thank you for the reply, but I think I am missing something.

I converted your code to this:

select *
FROM M2MDATA01.dbo.soitem soitem
where fsono in (select fsono from soitem where fpartno = 'PX1010')


as the database table is M2MDATA01.dbo.soitem, the sales order number column is 'fsono' and the part number column is 'fpartno'

And obviously, it doesn't work. What am I interpreting incorrectly? I am obviously a newb at SQL.

 

[lyontl]

Nevermind, I got it. It need the full name "M2MDATA01.dbo.soitem".

Thank you very much!!!

 

[lyontl]

Here is what I am up to:


select soitem.fpartno, soitem.fsono, soitem.fquantity, somast.forderdate
FROM M2MDATA01.dbo.soitem soitem, M2MDATA01.dbo.somast somast
where soitem.fsono = somast.fsono AND (soitem.fsono in (select soitem.fsono from M2MDATA01.dbo.soitem where soitem.fpartno = 'PX1010'))


Why would this only bring in part numbers that are PX1010? More imprtantly, How do I change it, so that it brings in all part numbers like the following codes does?

select soitem.fpartno, soitem.fsono, soitem.fquantity
FROM M2MDATA01.dbo.soitem soitem
where fsono in (select fsono from M2MDATA01.dbo.soitem where fpartno = 'PX1010')

 

[mrdenny]

It's got to have something to do with the join to the second table. Are you sure that, that is the correct column to do the join on?

Without having the schemas I can't tell what's wrong with the join.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com