Parent/Child Query: return single row from child table

Hello All,

I have a parent table ASSET:

CREATE TABLE [dbo].[ASSET](
[ASSETID] [int] IDENTITY(1,1) NOT NULL,
[ASSET_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]

with the following data:

INSERT INTO ASSET (ASSET_NAME) VALUES ('COMPANY1')
INSERT INTO ASSET (ASSET_NAME) VALUES ('COMPANY2')
INSERT INTO ASSET (ASSET_NAME) VALUES ('COMPANY3')

I also have a child table PRICING:

CREATE TABLE [dbo].[PRICING](
[PRID] [int] IDENTITY(1,1) NOT NULL,
[PHDATE] [datetime] NOT NULL CONSTRAINT [DF_PRICING_PHDATE] DEFAULT
(getdate()),
[PRICE] [float] NOT NULL,
[ASSETID] [int] NOT NULL,
CONSTRAINT [PK_PRICING] PRIMARY KEY CLUSTERED
(
[PRID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

with the following data:

INSERT INTO PRICING (PRICE, ASSETID) VALUES (100, 1)
INSERT INTO PRICING (PRICE, ASSETID) VALUES (150, 1)
INSERT INTO PRICING (PRICE, ASSETID) VALUES (175, 1)

I am trying to create the following output:

ASSET_NAME PHDATE PRICE
--------------------- ------------ ---------
COMPANY 1 timestamp 175
COMPANY 2 null null
COMPANY 3 null null

i.e. only return a single row from the PRICING table when joining the
ASSET table to the PRICING table.

Pointers appreciated
Thankyou!

On Feb 17, 7:30 pm, "Plamen Ratchev" wrote:
> Assuming you mean to get the latest pricing information for each asset based
> on the latest primary key in Pricing, then it will look like this:
>
> -- SQL Server 2000
> SELECT A.asset_name,
>           P.phdate,
>           P.price
> FROM Asset AS A
> LEFT OUTER JOIN
>      (SELECT assetid, phdate, price
>       FROM Pricing AS P1
>       WHERE prid = (SELECT MAX(prid)
>                           FROM Pricing AS P2
>                           WHERE P2.assetid = P1.assetid)) AS P
>   ON A.assetid = P.assetid
>
> -- SQL Server 2005
> SELECT A.asset_name,
>           P.phdate,
>           P.price
> FROM Asset AS A
> LEFT OUTER JOIN
>      (SELECT assetid, phdate, price,
>                 ROW_NUMBER() OVER(
>                         PARTITION BY assetid
>                        ORDER BY prid DESC) AS seq
>       FROM Pricing) AS P
>   ON A.assetid = P.assetid
>  AND P.seq = 1;
>
> -- SQL Server 2005
> SELECT A.asset_name,
>           P.phdate,
>           P.price
> FROM Asset AS A
> OUTER APPLY
>      (SELECT TOP(1) assetid, phdate, price
>       FROM Pricing AS P1
>       WHERE A.assetid = P1.assetid
>       ORDER BY prid DESC) AS P;
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

all set.
thankyou plamen!