select in 3 tables

Question

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

It's really difficult to tell what's going on in this question. Please rather add actual table structures and the actual query you used to get your output, as opposed to trying to explain it in words. — Bernhard Barker, Sep 28 '13 at 13:12

score 2 · Answer 1 · answered Sep 28 '13 at 13:21

2

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

answered Sep 28 '13 at 13:21

aaron

697
4
11

Looks, like the OP is using SQL Server, not MySQL, but I believe some versions of SQL Server may have an equivalent function. Besides that, though, you're relying on a dangerous 'feature' in MySQL that is only returning correct results due to the example data; always either 1) include columns as part of the `GROUP BY` clause or 2) make them part of an aggregate function. Anything else has the opportunity to go subtly wrong at unexpected times. – Clockwork-Muse Sep 28 '13 at 14:11
@Clockwork-Muse : Thanks for the info. But if i may ask, how may things go wrong if the 2 points you have mentioned are ignored. Is there a vulnerability that can take place? – aaron Sep 28 '13 at 14:17
1

Essentially, this is really easy to see if you have more than one index on a table, and the optimizer gets to choose. [This example](http://sqlfiddle.com/#!2/7df1f/1) uses two tables to hopefully be a little more clear (and easier for me to show). Now, if that was for one table... which of the two queries is returning correct results? Theoretically, results could change simply by _changing the order columns were listed in the _`SELECT` _statement_ (hint - that's bad - SQL isn't supposed to care about column order). It gets worse once you add joins and other stuff to the mix. – Clockwork-Muse Sep 28 '13 at 14:38
1

You need `FOR XML PATH` in SQL Server, [related question](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005). – Bernhard Barker Sep 28 '13 at 15:10

select in 3 tables

1 Answers1