Only n-Results per ID

[innu]

Hi all!

I have the following Problem.

I have a MS-SQL Datasource and 2 tables


Table1 Cars
ID - CAR
1 - VW
2 - Audi
3 - Mercedes
4 - Skoda

Table2 CarColor

IDCAR - Color
1 - red
1 - blue
1 - silver
2 - black
2 - green
3 - blue metallic
3 - black
3 - blue
3 - red
etc.

The query is so far:
select * from cars, carcolor where cars.id = carcolor.idcars


But now the User can say, he only wants 2 Results for one Car.

Par example:

The Result:

VW - red,blue
Audi - black,green
Mercedes - blue metallic, black

What have I to do, to get this result?

Should I make a SP with a cursor?



sl,
Albert

 

[jitter]

I would avoid using a cursor for performance reasons unless you absolutely have to. Looking at your example you might try exploring using the 'IN' clause for your 'Where' statement passing it the values you want returned. That is the first place I would start.

 

[Sudashan]

Hi innu,

create an view based on u r original query i.e
select * from cars, carcolor where cars.id = carcolor.idcars

Use the following query to get the desired result

select count(*) As rank, v.car, v.carcolour from viewname v, viewname v1 where
v.car + v.carcolour >= v1.car + v1.carcolour and v.car = v1.car
group by v.car, v.carcolour
having count(*) < 3;

Sudarshan

 

[tlbroadbent]

Here is another solution.

Select Car, Color
From Cars Inner Join CarColor
On Cars.id = CarColor.idcars
Where Color In
(Select Top 2 Color
From CarColor Where idcars=Cars.id)

You could also develop a stored procedure that would allow you to choose both the car and the number of colors to return.

Drop Procedure usp_CarColors
go

--The procedure usp_CarColors will return cars
--and colors from the corresponding tables.
--It dymically creates and executes a sql statement

--The SP has two parameters, @id and @cnt.
-- @id allows selection of a card by ID.
-- If no ID is chosen all cars will be returned.

-- @cnt is the number of colors to return per car.
-- If no number is provide, all colors will be returned.

--Usage examples:
-- exec usp_CarColors (return all cars, all colors)
-- exec usp_CarColors @id=1 (return car 1, all colors)
-- exec usp_CarColors @id=3,@cnt=2 (return car 3, 2 colors)
-- exec usp_CarColors @cnt=3 (return all cars, 3 colors)

Create Procedure usp_CarColors
@id int=0, @cnt int=0 As

Declare @sql nvarchar(200)

Select @sql=
'Select Car, Color' +
' From Cars Inner Join CarColor' +
' On Cars.id = CarColor.idcars' +
' Where ID=ID'

If @id>0
Begin
Select @sql=@sql + ' And ID=' + ltrim(str(@ID))
End

If @cnt>0
Begin
Select @sql=@sql +
' And Color In' +
' (Select Top ' + ltrim(str(@cnt)) + ' Color' +
' From CarColor Where idcars=Cars.id)'
End

--Print @sql

Exec (@sql)
go

------------------------------

You should add another column to the CarColor table to rank the colors in order of preference. Otherwise, SQL offers no guarantee of an ordered set. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.