Sub Query

[mandeep]

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.

[Pratap Prabhu]

/*
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'