Solved

Multi Table Select Question

Posted on 2008-06-11
5
186 Views
Last Modified: 2010-04-21
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



0
Comment
Question by:byteboy11
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:squillman
ID: 21765407
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
0
 
LVL 5

Expert Comment

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

Sorry 'bout that...
0
 
LVL 2

Accepted Solution

by:
howyue earned 250 total points
ID: 21765895
this would give u what u wan, for ur scenario 2 i change the str_id to 101, so that u get a combined data in the testing table. is there any other scenario that need to take care of?
/* Create testing table */

CREATE TABLE Sales_Detail (	Str_id INT,	Rgst_id INT, Tran_id INT, Tran_Ln_Num INT, QTY INT)

CREATE TABLE Sales_Detail_Discounts (Str_id INT, Rgst_id INT, Tran_id INT, Tran_Ln_Num INT, DESCR VARCHAR(50))
 

/* Insert testing value */

INSERT INTO Sales_Detail

SELECT 101, 77, 10, 1, 3 UNION ALL SELECT 101, 77, 10, 2, 1 UNION ALL SELECT 100, 77, 10, 1, 1 UNION ALL SELECT 100, 77, 10, 2, 1 UNION ALL SELECT 100, 77, 10, 3, 1

INSERT INTO Sales_Detail_Discounts

SELECT 101, 77, 10, 1, 'Discount#1' UNION ALL SELECT 101, 77, 10, 1, 'Discount#2' UNION ALL SELECT 100, 77, 10, 1, 'Discount#1' UNION ALL SELECT 100, 77, 10, 2, 'Discount#1' UNION ALL SELECT 100, 77, 10, 3, 'Discount#1'
 

/* Actual query */

SELECT d.Str_id, SUM(d.QTY) TotalQty, dd.DESCR

FROM Sales_Detail d

INNER JOIN (SELECT DISTINCT Str_id, Rgst_id, Tran_id, DESCR FROM Sales_Detail_Discounts) dd

	ON d.Str_id = dd.Str_id AND d.Rgst_id = dd.Rgst_id AND d.Tran_id = dd.Tran_id

GROUP BY d.Str_id, dd.DESCR

Open in new window

0
 

Author Closing Comment

by:byteboy11
ID: 31466663
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.
0
 

Author Comment

by:byteboy11
ID: 22926696
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

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now