Parent/Child Query: return single row child 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

SQL Server 2005 Forums

Transact-SQL (2005)

Parent/Child Query: return single row child table

Author

Topic

marly
Starting Member

6 Posts

Posted - 2008-09-08 : 18:07:26

I have a parent table called PurchaseOrder like this:

CREATE TABLE [dbo].[PurchaseOrder](
[POID] [int] IDENTITY(1,1) NOT NULL,
[PONumber] [varchar](50) NOT NULL
)

With the following data:

INSERT INTO PurchaseOrder (PONumber) VALUES ('PO123')
INSERT INTO PurchaseOrder (PONumber) VALUES ('PO124')
INSERT INTO PurchaseOrder (PONumber) VALUES ('PO125')

I also have a child table Product like this:

CREATE TABLE [dbo].[Product](
[PartNumberID] [int] IDENTITY(1,1) NOT NULL,
[POID] [int] NOT NULL,
[PartNumber] [varchar(50) NOT NULL
)

INSERT INTO Product (PartNumber, POID) VALUES ('PN987',1)
INSERT INTO Product (PartNumber, POID) VALUES ('PN986',1)
INSERT INTO Product (PartNumber, POID) VALUES ('PN987',2)
INSERT INTO Product (PartNumber, POID) VALUES ('PN983',2)
INSERT INTO Product (PartNumber, POID) VALUES ('PN986',3)
INSERT INTO Product (PartNumber, POID) VALUES ('PN982',3)

I'm trying to create a query that will return all the PO's that have PN987 in the child table, which should be PO123 & PO124 from the Parent table like this:

POID PONumber
----------------------
1 PO123
2 PO124

Or say, I wanted to change the search from PN987 and use PN986 it should look like this:

POID PONumber
----------------------
1 PO123
3 PO124

After working a bit, I came up with this:

SELECT PurchaseOrder.POID,
PurchaseOrder.PONumber,
Product.PartNumber
FROM POManagement.PurchaseOrder AS PurchaseOrder
OUTER APPLY
(SELECT TOP(1) POID, PartNumber
FROM POManagement.Product AS Product1
WHERE PurchaseOrder.POID = Product1.POID) as Product

Which seems to work ok, except I can't figure out how to put in something along the lines of "WHERE PartNumber = PN987" and return just the PO's that have PartNumber PN987.

Any suggestions or pointers would be much appreciated.

Thanks,

marly

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-08 : 20:32:08

SELECT po.POID, po.PONumber
FROM PurchaseOrder po
INNER JOIN Product p
ON po.POID = p.PO
WHERE p.PartNumber = 'PN987'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources