CODEWITHSUNDEEP
sqlsql-server
user603007
user603007

Reputation: 11794

how to combine these 2 queries to 1?

I would like to add a commaseperated column with a list of product categorynames for that product. This is my datamodel: enter image description here

I got all the productdetails in this query:

select d.name as 'Dealer Name',c.commissionamount as 'Commission Amount',c.createddate as 'Commission Created Date' ,p.name as 'Product Name'
from commission c
join dealer d 
on d.dealerid=c.dealerid
join product p on c.productid=p.productid
join productcategorymapping pcm on p.ProductId=pcm.ProductId
join ProductCategory pc on pc.ProductCategoryId=pcm.ProductCategoryId

I just want to add the csv column for each product with this query , I just dont know how to join the 2:

select stuff((
SELECT ',' + pc.Name
FROM ProductCategory pc
join ProductCategoryMapping pcm on pcm.ProductCategoryId=pc.ProductCategoryId
join Product p1 on p1.ProductId=pcm.ProductId
and p1.ProductId=1
FOR XML PATH('')),1,1,'');

It says productId=1 but this is where it needs to be joined to the other query. So the result would be something like:

DealerName  | Amount |date|product Name|csv
TestDealer1 | 105.25 |..  |ProdA       |CatA,CatC,CatF

I also created a sqlfiddle for this:http://sqlfiddle.com/#!6/d574e/4

Upvotes: 4

Views: 84

Answers (2)

M.Ali
M.Ali

Reputation: 69574

Try this ..

select
       d.name             AS [Dealer Name]
      ,c.commissionamount AS [Commission Amount]
      ,c.createddate      AS [Commission Created Date] 
      ,p.name             AS [Product Name]
      ,stuff((SELECT ', ' + pcci.Name
              FROM ProductCategory pcci
              join ProductCategoryMapping pcmi 
              on pcmi.ProductCategoryId=pcci.ProductCategoryId
              join Product p1i 
              on p1i.ProductId=pcmi.ProductId
              WHERE p1i.ProductId = p.productid
              FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'')
                           AS [CSV]

from commission c join dealer d 
on d.dealerid=c.dealerid
join product p 
on c.productid=p.productid;

Working SQL FIDDLE

Upvotes: 1

Neels
Neels

Reputation: 2543

It can be done just by combining the two in the Select statements like:

select DISTINCT d.name as 'Dealer Name',
c.commissionamount as 'Commission Amount',
c.createddate as 'Commission Created Date' ,
p.name as 'Product Name',
stuff((
SELECT ',' + pc.Name
FROM ProductCategory pc
join ProductCategoryMapping pcm on pcm.ProductCategoryId=pc.ProductCategoryId
join Product p1 on p1.ProductId=pcm.ProductId
and p1.ProductId=1
FOR XML PATH('')),1,1,'') as reqCol
from commission c
join dealer d 
on d.dealerid=c.dealerid
join product p 
on c.productid=p.productid
join productcategorymapping pcm 
on p.ProductId=pcm.ProductId
join ProductCategory pc 
on pc.ProductCategoryId=pcm.ProductCategoryId;

Check out http://sqlfiddle.com/#!6/d574e/12

Upvotes: 0

Related Questions