sistemas_casinomnes (11/28/2014)
Hello, I need assistance with a query that show me a Products Tables and its models, 1 product could have 2 or more models
its slow with sql - use the front end, but it can be done smth like this
declare @products table (id int identity primary key, name nvarchar(100))
declare @models table (idmodel int identity primary key, idproduct int, model nvarchar(100))
insert into @products (name) values ('Product 1'),('Product 2'),('Product 3')
insert into @models (idproduct,model) values (1,'Model 1'),(1,'Model 2'),(1,'Model 3'),(2,'Model 1'),(2,'Model 2'),(3,'Model 1')
;with result as (
select id,name, model, row_number() over (order by id,idmodel) rn from @products inner join @models on id=idproduct
)
select case when id=(select id from result b where b.rn=a.rn-1) then null else name end as product, model from result a