sql - Conditionally return values from LEFT JOIN between 3 tables based on CASE -

first off, apologies long post. it's more simple looks ;-)

i'm trying think conceptually simple, , believe i'm of way there, there's 1 last part can't implement without errors can't figure out how fix.

i have 3 related tables.

orders:
each row order unique id, there never duplicates.

+---------+---------+ | orderid | name    | +---------+---------+ | 1       | order 1 | | 2       | order 2 | | 3       | order 3 | +---------+---------+ 

order details:
relational table each row product line on order.

+---------+-----------+ | orderid | productid | +---------+-----------+ | 1       |         | | 2       | b         | | 2       | c         | | 3       |         | | 3       | b         | | 3       | b         | +---------+-----------+ 

as can see orders have 1 product (1), have multiple products (2) , have duplicate products (3).

products
each row product unique id, there never duplicates.

+-----------+-------------+ | productid | description | +-----------+-------------+ |         | chicken     | | b         | fish        | | c         | beef        | +-----------+-------------+ 

i want return rows orders table , conditionally return information related products in 1 column.

the condition @ how many distinct products each order has. if it's 1 want return product description value. if it's more 1 want return placeholder text such 'multi'.

i think need use case working, can't figure out.

i can count unique products this:

select      o.name    ,count(distinct d.productid) 'unique products' orders o  left join orderdetails d on o.orderid = d.orderid left join products p on d.productid = p.productid  group o.name order o.name desc  go 

results this:

+---------+-----------------+ | name    | unique products | +---------+-----------------+ | order 1 | 1               | | order 2 | 2               | | order 3 | 2               | +---------+-----------------+ 

what want this:

+---------+-----------------+ | name    | unique products | +---------+-----------------+ | order 1 | chicken         | | order 2 | multi           | | order 3 | multi           | +---------+-----------------+ 

i have been trying use case believe i've gotten correct:

case when (count(distinct d.productid)) > 1 'multi' else p.description end 'products' 

however unless add p.description group by error (which understand):

column 'product.description' invalid in select list because not contained in either aggregate function or group clause.

but if add results aren't want, example:

+---------+----------+ | name    | products | +---------+----------+ | order 1 | chicken  | | order 2 | fish     | | order 2 | beef     | | order 3 | chicken  | | order 3 | fish     | | order 3 | fish     | +---------+----------+ 

when should "order 2 - multi" on 1 row example. this bit don't understand.

if can on bit alone solve problem , i'd accept answer. however...

bonus round

the above fine , all, if bit possible i'd accept answer above others.

can concatenate product names? i've been looking @ coalesce , for xml path can't wrap head around them @ don't have code show.

results this:

+---------+--------------+ | name    | products     | +---------+--------------+ | order 1 | chicken      | | order 2 | fish;beef    | | order 3 | chicken;fish | +---------+--------------+ 

if you've made far commend you! thanks!

you pretty close. need case logic , aggregation function around description:

select o.name,        (case when count(distinct d.productid)  = 1              max(p.description)              else 'multi'         end) descriptions orders o left join      orderdetails d      on o.orderid = d.orderid left join      products p      on d.productid = p.productid group o.name order o.name desc 

the second part different question. in sql server, need use xml subquery:

select o.name,        stuff((select distinct ',' + p.description               orderdetails d left join                    products p                    on d.productid = p.productid               o.orderid = d.orderid                xml path (''), type              ).value('.', 'nvarchar(max)'                     ), 1, 1, ''             ) descriptions orders o order o.name desc