asked on

Multi Table Select Question

I have 2 tables. Sales_Detail and Sales_Detail_Discounts.

Sales_Detail Table:
Str_id * Rgst_id* Tran_id * Tran_Ln_Num * QTY
----------------------------------------------------------
100 77 10 1 3
100 77 10 2 1

Sales_Detail_Discounts
Str_id * Rgst_id* Tran_id * Tran_Ln_Num * DESCR
------------------------------------------------------------
100 77 10 1 Discount#1

The Sales_Detail and the Sales_Detail_Discounts table are joined by Str_id,Rgst_id,Tran_id,Tran_ln_num

What I want is a sum of the QTY from the Sales_Detail Table for each unique Str_id and DESCR grouped in 1 line like:

Str_id * Qty * DESCR
----------------------------
100 4 Discount#1

Here is the statment I'm using:
Select        SD.str_id,sum(sd.qty),SDD.disc_descr
From sales_detail SD
left outer Join Sales_Detail_discounts SDD
On SD.str_id = SDD.str_id
And SD.rgst_id = SDD.rgst_id
And SD.tran_id = SDD.tran_id
-- And SD.tran_ln_num = SDD.tran_ln_num
                          and      sd.tran_ln_num = sdd.tran_ln_num
                          INNER            JOIN      LOC L
                                    ON      L.LOC_ID                    = SD.STR_ID
Where Exists ( SELECT str_id
FROM Sales_Detail_discounts SDD1
WHERE SD.STR_ID = SDD1.STR_ID
AND SD.RGST_ID = SDD1.RGST_ID
AND SD.TRAN_ID = SDD1.TRAN_ID )
Group By SD.STR_ID,SDD.disc_descr

In the above Example it does work and I do get the correct values of:
Str_id * Qty * DESCR
----------------------------
100 4 Discount#1

BUT when my data is like this I dont get correct data:
Sales_Detail Table:
Str_id * Rgst_id* Tran_id * Tran_Ln_Num * QTY
----------------------------------------------------------
100 77 10 1 1
100 77 10 2 1
100 77 10 3 1

Sales_Detail_Discounts
Str_id * Rgst_id* Tran_id * Tran_Ln_Num * DESCR
------------------------------------------------------------
100 77 10 1 Discount#1
100 77 10 2 Discount#1
100 77 10 3 Discount#1

In this case I get:
Str_id * Qty * DESCR
----------------------------
100 9 Discount#1

When it should be:
Str_id * Qty * DESCR
----------------------------
100 3 Discount#1

----------------------------------------------------------------------------------
ALSO to complicate matters I could have a matching str_id,rgst_id,tran_id,tran_ln_num in the sales_detail_discounts but with a different DESCR such as:
Sales_Detail Table:
Str_id * Rgst_id* Tran_id * Tran_Ln_Num * QTY
----------------------------------------------------------
100 77 10 1 3
100 77 10 2 1

Sales_Detail_Discounts
Str_id * Rgst_id* Tran_id * Tran_Ln_Num * DESCR
------------------------------------------------------------
100 77 10 1 Discount#1
100 77 10 1 Discount#2

In this case I would like to see:
Str_id * Qty * DESCR
----------------------------
100 4 Discount#1
100 4 Discount#2

Thanks. If you need more info feel free to ask

squillman

Hi byteboy11,

This query gives you your desired results in each of your cases above:

SELECT
      Detail.Str_id,
      SUM(Detail.Qty) AS Qty,
      Discounts.DESCR
FROM
      (SELECT Str_id,Qty FROM #Detail) AS Detail
      INNER JOIN
      (SELECT DISTINCT Str_id,DESCR FROM [#discounts]) AS Discounts
      ON Detail.Str_id=Discounts.Str_id
group BY Detail.Str_id,Discounts.DESCR

squillman

Oops, forgot to change the table names back:
Replace #Detail with Sales_Detail
Replace #Discounts with Sales_Detail_Discounts

Sorry 'bout that...

ASKER CERTIFIED SOLUTION

howyue

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

byteboy11

ASKER

Thanks howyue, this indeed does give me what I want. Do you mind kind of explaining more or less how the statement works. I mean just so i can get an understanding. Thanks.

byteboy11

ASKER

I made what I'm looking for a little more straight forward and created the scripts to represent the problem.

/* CREATE 2 TABLES */
CREATE TABLE [dbo].[a_sales](
	[style] [varchar](50) NULL,
	[str_id] [int] NULL,
	[rgst_id] [int] NULL,
	[tran_id] [int] NULL,
	[tran_ln_num] [int] NULL,
	[qty] [int] NULL,
	[sum_of_disc] [money] NULL
) ON [PRIMARY]
 
CREATE TABLE [dbo].[a_discounts](
	[str_id] [int] NULL,
	[rgst_id] [int] NULL,
	[tran_id] [int] NULL,
	[tran_ln_num] [int] NULL,
	[disc_descr] [varchar](50) NULL
) ON [PRIMARY]
 
/* POPULATE 2 TABLES */
INSERT INTO a_sales
SELECT 'item#1', 100,1,500,1,2,6.00 UNION ALL 
SELECT 'item#2', 100,2,501,1,3,8.00   
 
INSERT INTO a_Discounts
SELECT 100, 1, 500, 1, 'linedisc1' UNION ALL 
SELECT 100, 1, 500, 1, 'linedisc2' UNION ALL 
SELECT 100, 1, 500, 1, 'linedisc3' UNION ALL 
SELECT 100, 2, 501, 1, 'linedisc1' 
 
/* DESIRED RESULT */
item		disc_name	Qty	sum_of_disc
----		---------	---	-----
item#1		linedisc1	2	$6.00
item#1		linedisc2	0	$0.00
item#1		linedisc3	0	$0.00
item#2		linedisc1	3	$8.00
 
 
-The thing is that I only want to display the sum_of_disc for the FIRST line of the matching str_id/rgst_id/tran_id/tran_ln_num
and show 0 QTY AND $0.00 sum_of_disc for subsequent rows of the matching str_id/rgst_id/tran_id/tran_ln_num.

Open in new window