Query from Multiple tables... - 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 2000 Forums

Transact-SQL (2000)

Query from Multiple tables...

Author

Topic

nbop
Starting Member

3 Posts

Posted - 2003-06-13 : 07:03:08

Hi I have a problem It's probably an easy fix but right now I must have missed something...can someone help me out here??

I have Three Tables

tblBrands
id(PK)
brand

tblModels
id(PK)
model
description
fk_brand_id

tblVersions
id
version
price
fk_model_id

In my record I want to get All Models of a certain Brand and it should also show the price of the cheapest version of each model
from this manufacturer...

HELP

/M

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-13 : 08:16:35

Homework is it? ;)

SELECT distinct
model, lowest_price
FROM
tblModels
INNER JOIN tblBrands
ON fk_brand_id = tblBrands.[id]
INNER JOIn
(SELECT min (price) as lowest_price, fk_model_id
FROM tblVersions GROUP BY fk_model_id)
on fk_model_id = tblModels.[id]
on
where brand = 'the brand you are looking for'

Should get you started.

-------
Moo.

dsdeming

479 Posts

Posted - 2003-06-13 : 08:29:29

Beaten to the punch, yet again.

But since I went to all this trouble, I'm posting anyway.

set nocount on

create table tblBrands (
id int identity primary key,
brand varchar( 10 ))

create table tblModels (
id int identity primary key,
model varchar( 10 ),
description varchar( 10 ),
fk_brand_id int references tblBrands(id ))

create table tblVersions (
id int identity primary key,
version varchar( 10 ),
price money,
fk_model_id int references tblModels(id ))

insert into tblBrands values( 'PondWater' )
insert into tblBrands values( 'Hayseed' )

insert into tblModels values( 'ST001', 'something', 1 )
insert into tblModels values( 'ST002', 'else', 2 )

insert into tblVersions values( 'v1.0', 85.00, 1 )
insert into tblVersions values( 'v1.1', 88.00, 1 )
insert into tblVersions values( 'v1.11', 84.00, 1 )
insert into tblVersions values( 'v1.0', 7.12, 2 )
insert into tblVersions values( 'v1.1', 7.07, 2 )
insert into tblVersions values( 'v2.0', 7.18, 2 )
insert into tblVersions values( 'v1.2', 7.00, 2 )
insert into tblVersions values( 'v3.0', 6.88, 2 )

SELECT b.id,
m.id,
m.model,
MIN( v.price )
FROM tblBrands b
JOIN tblModels m ON b.id = m.fk_brand_id
LEFT OUTER JOIN tblVersions v ON m.id = v.fk_model_id
-- WHERE b.id = 1
GROUP BY
b.id,
m.id,
m.model

drop table tblVersions
drop table tblModels
drop table tblBrands

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources