There are a lot of order parameters, is it possible to join them in the "one" pass?

select * from [order] cross join ( select max(case when id_par=1 then value end) p1, max(case when id_par=2 then value end) p2 -- .... from orderparams where orderparams.id_order = [order].id ) t

There is an error in the last line, because it is impossible to "inside" the request to send [order] .id, it is not available there. Is it possible to somehow link these tables in this way without using a function or grouping? (mssql-server 2005) Parameters of the order of 10-20 pieces.

  • one
    Make a gorup by id_order in the subquery, add this field to the select list and execute the usual ON t.id_order=[order].id . Or look in the direction of cross apply, he did not work with him, but something suggests that this is what you need - Mike
  • Yes, instead of cross join cross apply suitable. - i-one
  • And, apply is available only from the 2008 server ... and the sample does not imply that you can somehow limit the number of records in advance? In the same place, an implicit grouping on each separate subquery still happens, for the sake of max. And you can do without a subquery, and glue the tables directly. After all, in order for sure, only one entry per group from params. - Mike
  • My 2005 v 9.0 apply works. - nick_n_a

1 answer 1

Method 1 - pull the id_order outside, then you will not need the [order].id inside:

 select * from [order] inner join (...) t on [order].id = t.id_order

Method 2 - use cross apply :

 select * from [order] cross apply ( select ... where id_order = [order].id ) t
  • With the 10th left join 10 pieces of parameters 23 seconds, c [1] group 7 seconds (surprised) from [2] cross apply 16 seconds. Table params 900000 records, order 500000 records (base working online ). - nick_n_a