Pulling data from 2 tables, 1 with possibly multiple records

I'm trying to create a view which pulls data from the following 2 tables:
CREATE TABLE PC (
PCName varchar(50) NOT NULL,
Make varchar(50),
Model varchar(50),
SerialNumber varchar(50)
PRIMARY KEY
(PCName)
)
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
'OptiPlex GX1', '12345')
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
'PowerEdge 6450', '23456')
CREATE TABLE CPU (
PCName varchar(50) NOT NULL REFERENCES PC(PCName),
Row int NOT NULL,
Type varchar(50),
Speed int
PRIMARY KEY
(PCName, Row)
)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
III', 1000)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium III
Xeon', 700)
The CPU table holds an entry for each CPU in the PC. Now I want the view to
retrieve 1 row per PC and look like this...
PCName Make Model SerialNumber Type
Speed NumberOfCPUs
========================================
==============================
TEST1 Dell OptiPlex GX1 12345 Pentium III
1000 1
TEST2 Dell PowerEdge 6450 23456 Pentium III
Xeon 1200 4
... where NumberOfCPUs is the max(Row) for each particular PC.
Any ideas? Unfortunately we are stuck with the table structure as is (from
a 3rd party).
ThanksJim
Thanks for posting DDL
See , if this helps you
SELECT PC.PCName,Make,Model,SerialNumber,
Type, Speed,NumberOfCPUs FROM PC JOIN
(
SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
GROUP BY PCName,Type,Speed
) AS Der ON PC.PCName=Der.PCName
"Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> I'm trying to create a view which pulls data from the following 2 tables:
> CREATE TABLE PC (
> PCName varchar(50) NOT NULL,
> Make varchar(50),
> Model varchar(50),
> SerialNumber varchar(50)
> PRIMARY KEY
> (PCName)
> )
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
> 'OptiPlex GX1', '12345')
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
> 'PowerEdge 6450', '23456')
> CREATE TABLE CPU (
> PCName varchar(50) NOT NULL REFERENCES PC(PCName),
> Row int NOT NULL,
> Type varchar(50),
> Speed int
> PRIMARY KEY
> (PCName, Row)
> )
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
> III', 1000)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium
> III Xeon', 700)
>
> The CPU table holds an entry for each CPU in the PC. Now I want the view
> to retrieve 1 row per PC and look like this...
> PCName Make Model SerialNumber Type Speed
> NumberOfCPUs
> ========================================
==============================
> TEST1 Dell OptiPlex GX1 12345 Pentium
> III 1000 1
> TEST2 Dell PowerEdge 6450 23456 Pentium III
> Xeon 1200 4
>
> ... where NumberOfCPUs is the max(Row) for each particular PC.
> Any ideas? Unfortunately we are stuck with the table structure as is
> (from a 3rd party).
> Thanks
>|||Yes, that certainly does. Thank you very much.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uMIH0gmvFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Jim
> Thanks for posting DDL
> See , if this helps you
>
> SELECT PC.PCName,Make,Model,SerialNumber,
> Type, Speed,NumberOfCPUs FROM PC JOIN
> (
> SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
> GROUP BY PCName,Type,Speed
> ) AS Der ON PC.PCName=Der.PCName
>
> "Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
> news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>