-
Sub Query
Hi
i have table called car.. that consist of
id, photo,brand,model,photo.
I want to display the photo of all the cars that have brand suppose KIA. As the brands may consists of many model.
So the main query is -- I want to display the photo of only one car from each model of brand KIA..
suppose: i have values like..
brands = KID model = 2
brands = KID model = 2
brands = KID model = 2
brands = KID model = 3
brands = KID model = 4
I donot want to display all but only one car from each model of brand KIA.
I was using query like
select * from make where brand='KIA' and model in (select (model) from make group by model.
But is not working correctly.
Plz clear it.
-
Sub Query - Try this
Code:
/*
Notes:
1. @Cars is a table variable, I have used to simulate your Cars table
2. I have inserted records for KIA and BENZ so that we have multiple brands
*/
declare @Cars table
(id int identity
,Brand varchar(100)
,Model int
,photo varchar(100)
)
insert into @Cars(Brand,Model,photo)
(select 'KIA',2,'' union all
select 'KIA',2,'' union all
select 'KIA',3,'' union all
select 'KIA',4,'' union all
select 'Benz',21,'' union all
select 'Benz',21,'' union all
select 'Benz',31,'' union all
select 'Benz',41,''
)
--All Brands
select
cars1.*
from
@Cars Cars1
join ( select ID=min(ID)
,brand
,model
from @Cars
group by brand,model
) Cars2
on cars1.ID=Cars2.ID
--KIA Only
select
cars1.*
from
@Cars Cars1
join ( select ID=min(ID)
,brand
,model
from @Cars
group by brand,model
) Cars2
on cars1.ID=Cars2.ID
where
Cars1.Brand='KIA'