CODEWITHSUNDEEP
sqlsql-server
user2781200
user2781200

Reputation: 13

select in 3 tables

i have 3 table 

table 1 "Sale" that saved date of user sale 
table 2 "ProductFactor" that saved factorid and productid in wich factoreid
tabel 3 "Product" that saved productid and product name

i want select in 3 table for this result:

show user factorid +price+ saledate + any product name that avalibe in this factorid

but when do it, for example when in one factorid that have 3 productid, is show this:

 date       factoreid    name     price 

2013-09-25    1         x         18261256            
2013-09-25    1         y         2365560.0000
2013-09-25    2         w         5500.0000
2013-09-25    3         z         50000.0000
2013-09-25    1         k         324.0000

i want show this:

date    factoreid    name     price 

2013-09-25    1    x,y,k      sum of 3 product name           
2013-09-25    2     w       5500.0000
2013-09-25    3     z       50000.0000

CREATE PROCEDURE [dbo].[GetUserSaleReport] 
@CurrentUserId uniqueidentifier
AS
BEGIN
SELECT dbo.Sale.SaleDate,dbo.ProductFactor.FactoreId,dbo.Product.ProductName,dbo.Product.Price
 FROM Sale INNER JOIN ProductFactor ON 
 dbo.Sale.FactoreId=dbo.ProductFactor.FactoreId
 INNER JOIN dbo.Product ON dbo.ProductFactor.ProductId=dbo.Product.ProductId 
 WHERE dbo.Sale.UserId = @CurrentUserId AND dbo.Sale.FactoreId=dbo.ProductFactor.FactoreId AND dbo.ProductFactor.ProductId=dbo.Product.ProductId
 ORDER BY dbo.Sale.SaleDate
END

Upvotes: 0

Views: 54

Answers (1)

aaron
aaron

Reputation: 687

This could be your query:

select date, factoreid,GROUP_CONCAT(name) , sum(price)
from tablename 
group by factoreid

Refer: http://sqlfiddle.com/#!2/284fd/5

Upvotes: 2

Related Questions