Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple Tables to Create a More Complete List

Author  Topic 

Bau_JJones
Starting Member

13 Posts

Posted - 2014-06-26 : 13:07:10
Greetings to all. I've worked with Crystal Reports for more than 10 years now, but I've never had any formal training on it. My SQL knowledge is limited to only trial-and-error and on-the-fly learning, sadly. However, I've had past experience with VB and some earlier, more basic programming, so I don't find it that difficult to grasp; it's just rather overwhelming without any formalized training.

I'm trying now to solve a problem that involves a sales history report. Our sales history table lists only records for product lines which a customer has purchased in the past. However, the budgeting team needs a list of each customer with all product lines, even if they've never purchased, and they'd like to include an industry code value that is from another table.

So the way the tables are looking is this:

Sales_History as the main table with the appropriate data in it
Product_Lines as a reference to fill out the main table with non-purchased lines
Accounts as another reference that gives the industry code per the customer ID in the main table

I'm seemingly on the right track with the following, which tries to create a list of all product lines and the records from sales_history which match them:

SELECT sales_history.sah_sort_id,sales_history.sah_sortkey3,sales_history.sah_sortkey2_desc,
prod_lines.pl_prodline_id,prod_lines.pl_short_desc
FROM dbo.sales_history
INNER JOIN prod_lines ON sales_history.sah_sortkey3=prod_lines.pl_prodline_id

UNION ALL

SELECT CAST(NULL AS VARCHAR(20)), CAST(NULL AS VARCHAR(20)), CAST(NULL AS VARCHAR(20)),
prod_lines.pl_prodline_id,prod_lines.pl_short_desc
FROM prod_lines
WHERE NOT EXISTS (
SELECT * FROM sales_history
WHERE sales_history.sah_sortkey3 = prod_lines.pl_prodline_id)

ORDER BY pl_prodline_id,sah_sortkey2_desc

However, this produces a list of only 21 rows, when there should be 41.

I feel as I've summarized this that I am looking at the problem from completely the wrong direction, so any advice would be most appreciated.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-26 : 13:15:57
Try LEFT JOIN instead. If you need further help, please post sample data, expected output and DDL for the tables involved. You can use this link to see how to post data/code to get the best help: http://www.sqlservercentral.com/articles/Best+Practices/61537/.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-26 : 15:37:11
Try this:
select a.sah_sort_id
,a.sah_sortkey3
,a.sah_sortkey2_desc
,b.pl_prodline_id
,b.pl_shortdesc
from sales_history as a
full outer join prod_lines as b
on b.pl_prodline_id=a.sah_sortkey3
order by b.pl_prodline_id
,a.sah_sortkey2_desc
Go to Top of Page

Bau_JJones
Starting Member

13 Posts

Posted - 2014-06-26 : 16:52:25
Hi tkizer,

Thank you for the response and the gentle nudge in the correct direction!

I have found that no changing of the JOIN makes a difference

The code below will create a sample set of data from the Sales History table, which contains all the important sales figures. I believe I can work out those figures later so I've left them out of the sample set here.

--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb.#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
FLT VARCHAR(3),
PL VARCHAR(30),
CUST VARCHAR(75),
)

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, FLT, PL, CUST)
SELECT '1','Z37','10100 ','21st Century' UNION ALL
SELECT '2','Z37','10300 ','21st Century' UNION ALL
SELECT '3','Z37','10700 ','21st Century' UNION ALL
SELECT '4','Z37','20300 ','21st Century' UNION ALL
SELECT '5','Z37','30150 ','21st Century' UNION ALL
SELECT '6','Z37','30900 ','21st Century' UNION ALL
SELECT '7','Z37','40900 ','21st Century'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable ON

SELECT v.*
FROM #MyTable v
ORDER BY PL


The next bit of code will create the complete 41-row sample set of all applicable product lines from the Product Lines table.

--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb.#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
PL INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SDESC VARCHAR(75)
)

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable OFF

--===== Insert the test data into the test table
INSERT INTO #mytable
(PL, SDESC)
SELECT '10100 ','S-IS: Inductive Sensors' UNION ALL
SELECT '10150 ','S-CS: Capacitive Sensors' UNION ALL
SELECT '10200 ','S-US: Ultrasonic Sensors' UNION ALL
SELECT '10250 ','S-MS: My-Com Switches' UNION ALL
SELECT '10300 ','S-PS: Photoelectric Sensors' UNION ALL
SELECT '10600 ','S-AS: Magnetic Sensors' UNION ALL
SELECT '10700 ','S-AC: Accessories' UNION ALL
SELECT '10800 ','S-SS: Sensor Systems' UNION ALL
SELECT '10900 ','S-OT: SE-Other Products/Svcs' UNION ALL
SELECT '20100 ','M-IO: Inc. Encoders optical' UNION ALL
SELECT '20150 ','M-IM: Inc. Encoders magnetic' UNION ALL
SELECT '20200 ','M-AO: Abs. Encoders optical' UNION ALL
SELECT '20250 ','M-AM: Abs. Encoders magnetic' UNION ALL
SELECT '20300 ','M-MS: Encoders Without Bearing' UNION ALL
SELECT '20350 ','M-TR: Tach. & Resolvers' UNION ALL
SELECT '20400 ','M-SS: Speed Limit Switches' UNION ALL
SELECT '20450 ','M-CO: Counters' UNION ALL
SELECT '20500 ','M-DS: Drive & Spindle Position' UNION ALL
SELECT '20600 ','M-IS: Inclinometers' UNION ALL
SELECT '20900 ','M-OT: MC - Other Products/Svcs' UNION ALL
SELECT '30100 ','V-DC: Digital Camera' UNION ALL
SELECT '30150 ','V-SV: Smart Vision' UNION ALL
SELECT '30200 ','V-IP: Image Processing Equip.' UNION ALL
SELECT '30250 ','V-VS: Vision Systems' UNION ALL
SELECT '30300 ','V-IM: Inspection Machines' UNION ALL
SELECT '30350 ','V-PS: Vision Proj. & Sys. Int.' UNION ALL
SELECT '30900 ','V-OT: VT - Other Products/Svcs' UNION ALL
SELECT '40100 ','P-PE: Pressure electronic' UNION ALL
SELECT '40150 ','P-PM: Pressure mechanical' UNION ALL
SELECT '40200 ','P-TE: Temperature electronic' UNION ALL
SELECT '40250 ','P-TM: Temperature mechanical' UNION ALL
SELECT '40300 ','P-AL: Level' UNION ALL
SELECT '40500 ','P-FS: Force & Strain Sensors' UNION ALL
SELECT '40850 ','P-HW: Haenni Wheel Load Scales' UNION ALL
SELECT '40900 ','P-OT: PI - Other Products/Svcs' UNION ALL
SELECT '50100 ','G-CG: Cold Glue' UNION ALL
SELECT '50150 ','G-HM: Hot Melt' UNION ALL
SELECT '50200 ','G-CQ: Gluing Controller & QA' UNION ALL
SELECT '50910 ','G-SE: GS - Services' UNION ALL
SELECT '50920 ','G-OT: GS - Other Products' UNION ALL
SELECT '90100 ','COMA: Contract Manufacturing'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF

SELECT v.*
FROM #MyTable v
ORDER BY PL


Finally the code below will produce a short list of sample customers from the Customers table (I added an ID column here because our database has non-integer primary keys):

--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb.#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
U_ID INT IDENTITY(1,1),
CUST VARCHAR(9) PRIMARY KEY CLUSTERED,
NAME VARCHAR(35),
GRP VARCHAR (25)
)

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INSERT INTO #mytable
(U_ID,CUST, NAME, GRP)
SELECT '1','C1491','Korber Medipak NA Inc.','' UNION ALL
SELECT '2','C1492','R.R. Donnelley South','900' UNION ALL
SELECT '3','C1493','21st Century','' UNION ALL
SELECT '4','C1494','Integrated Power Services','' UNION ALL
SELECT '5','C1495','NOV ASEP Elmar (National Oilwell)',''

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF

SELECT v.*
FROM #MyTable v
ORDER BY NAME


I am not sure how to create a table of my expected result, so here is a tab-separated text list of what I'm trying to do:

Customers.Name Product Lines.PL Product Lines.Description Sales History.Filter Sales History.PL Sales History.Customer
21st Century 10100 S-IS: Inductive Sensors Z37 10100 21st Century
21st Century 10150 S-CS: Capacitive Sensors
21st Century 10200 S-US: Ultrasonic Sensors
21st Century 10250 S-MS: My-Com Switches
21st Century 10300 S-PS: Photoelectric Sensors Z37 10300 21st Century
21st Century 10600 S-AS: Magnetic Sensors
21st Century 10700 S-AC: Accessories Z37 10700 21st Century
21st Century 10800 S-SS: Sensor Systems
21st Century 10900 S-OT: SE-Other Products/Svcs
21st Century 20100 M-IO: Inc. Encoders optical
21st Century 20150 M-IM: Inc. Encoders magnetic
21st Century 20200 M-AO: Abs. Encoders optical
21st Century 20250 M-AM: Abs. Encoders magnetic
21st Century 20300 M-MS: Encoders Without Bearing Z37 20300 21st Century
21st Century 20350 M-TR: Tach. & Resolvers
21st Century 20400 M-SS: Speed Limit Switches
21st Century 20450 M-CO: Counters
21st Century 20500 M-DS: Drive & Spindle Position
21st Century 20600 M-IS: Inclinometers
21st Century 20900 M-OT: MC - Other Products/Svcs
21st Century 30100 V-DC: Digital Camera
21st Century 30150 V-SV: Smart Vision Z37 30150 21st Century
21st Century 30200 V-IP: Image Processing Equip.
21st Century 30250 V-VS: Vision Systems
21st Century 30300 V-IM: Inspection Machines
21st Century 30350 V-PS: Vision Proj. & Sys. Int.
21st Century 30900 V-OT: VT - Other Products/Svcs Z37 30900 21st Century
21st Century 40100 P-PE: Pressure electronic
21st Century 40150 P-PM: Pressure mechanical
21st Century 40200 P-TE: Temperature electronic
21st Century 40250 P-TM: Temperature mechanical
21st Century 40300 P-AL: Level
21st Century 40500 P-FS: Force & Strain Sensors
21st Century 40850 P-HW: Haenni Wheel Load Scales
21st Century 40900 P-OT: PI - Other Products/Svcs Z37 40900 21st Century
21st Century 50100 G-CG: Cold Glue
21st Century 50150 G-HM: Hot Melt
21st Century 50200 G-CQ: Gluing Controller & QA
21st Century 50910 G-SE: GS - Services
21st Century 50920 G-OT: GS - Other Products
21st Century 90100 COMA: Contract Manufacturing
Go to Top of Page

Bau_JJones
Starting Member

13 Posts

Posted - 2014-06-26 : 16:53:42
That tab-separated list looks rather ugly, so if you can tell me a better way to present the expected result I will reformat it.

Thank you.
Go to Top of Page

kostya1122
Starting Member

15 Posts

Posted - 2014-06-26 : 18:50:11
try to
cross join your costumer table and product lines table

then join sales history to the outcome of the cross join
Go to Top of Page

Bau_JJones
Starting Member

13 Posts

Posted - 2014-06-27 : 08:11:01
quote:
Originally posted by bitsmed

Try this:
select a.sah_sort_id
,a.sah_sortkey3
,a.sah_sortkey2_desc
,b.pl_prodline_id
,b.pl_shortdesc
from sales_history as a
full outer join prod_lines as b
on b.pl_prodline_id=a.sah_sortkey3
order by b.pl_prodline_id
,a.sah_sortkey2_desc




Thank you for the reply, bitsmed, but I'm not sure how I can join those two tables without a common column. I guess I could create a table using the methods in posting my codes and adding an ID column, but I imagine there is an easier way.

I'd say my first, vague post was not nearly clear enough.
Go to Top of Page

Bau_JJones
Starting Member

13 Posts

Posted - 2014-06-27 : 08:13:21
quote:
Originally posted by kostya1122

try to
cross join your costumer table and product lines table

then join sales history to the outcome of the cross join



Hello kostya1122. Thanks for your reply.

I've done a cross join with Customers and Prod_lines. The code below will produce a table that shows the results, limited to one customer, 21st Century (for the sake of simplicity).

--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb.#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
ID VARCHAR(5),
NAME VARCHAR(30),
SG VARCHAR(3),
PL INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PL_DESC VARCHAR(45)
)

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, NAME, SG, PL, PL_DESC)
SELECT 'C1493','21st Century',' ','10100 ','S-IS: Inductive Sensors' UNION ALL
SELECT 'C1493','21st Century',' ','10150 ','S-CS: Capacitive Sensors' UNION ALL
SELECT 'C1493','21st Century',' ','10200 ','S-US: Ultrasonic Sensors' UNION ALL
SELECT 'C1493','21st Century',' ','10250 ','S-MS: My-Com Switches' UNION ALL
SELECT 'C1493','21st Century',' ','10300 ','S-PS: Photoelectric Sensors' UNION ALL
SELECT 'C1493','21st Century',' ','10600 ','S-AS: Magnetic Sensors' UNION ALL
SELECT 'C1493','21st Century',' ','10700 ','S-AC: Accessories' UNION ALL
SELECT 'C1493','21st Century',' ','10800 ','S-SS: Sensor Systems' UNION ALL
SELECT 'C1493','21st Century',' ','10900 ','S-OT: SE-Other Products/Svcs' UNION ALL
SELECT 'C1493','21st Century',' ','20100 ','M-IO: Inc. Encoders optical' UNION ALL
SELECT 'C1493','21st Century',' ','20150 ','M-IM: Inc. Encoders magnetic' UNION ALL
SELECT 'C1493','21st Century',' ','20200 ','M-AO: Abs. Encoders optical' UNION ALL
SELECT 'C1493','21st Century',' ','20250 ','M-AM: Abs. Encoders magnetic' UNION ALL
SELECT 'C1493','21st Century',' ','20300 ','M-MS: Encoders Without Bearing' UNION ALL
SELECT 'C1493','21st Century',' ','20350 ','M-TR: Tach. & Resolvers' UNION ALL
SELECT 'C1493','21st Century',' ','20400 ','M-SS: Speed Limit Switches' UNION ALL
SELECT 'C1493','21st Century',' ','20450 ','M-CO: Counters' UNION ALL
SELECT 'C1493','21st Century',' ','20500 ','M-DS: Drive & Spindle Position' UNION ALL
SELECT 'C1493','21st Century',' ','20600 ','M-IS: Inclinometers' UNION ALL
SELECT 'C1493','21st Century',' ','20900 ','M-OT: MC - Other Products/Svcs' UNION ALL
SELECT 'C1493','21st Century',' ','30100 ','V-DC: Digital Camera' UNION ALL
SELECT 'C1493','21st Century',' ','30150 ','V-SV: Smart Vision' UNION ALL
SELECT 'C1493','21st Century',' ','30200 ','V-IP: Image Processing Equip.' UNION ALL
SELECT 'C1493','21st Century',' ','30250 ','V-VS: Vision Systems' UNION ALL
SELECT 'C1493','21st Century',' ','30300 ','V-IM: Inspection Machines' UNION ALL
SELECT 'C1493','21st Century',' ','30350 ','V-PS: Vision Proj. & Sys. Int.' UNION ALL
SELECT 'C1493','21st Century',' ','30900 ','V-OT: VT - Other Products/Svcs' UNION ALL
SELECT 'C1493','21st Century',' ','40100 ','P-PE: Pressure electronic' UNION ALL
SELECT 'C1493','21st Century',' ','40150 ','P-PM: Pressure mechanical' UNION ALL
SELECT 'C1493','21st Century',' ','40200 ','P-TE: Temperature electronic' UNION ALL
SELECT 'C1493','21st Century',' ','40250 ','P-TM: Temperature mechanical' UNION ALL
SELECT 'C1493','21st Century',' ','40300 ','P-AL: Level' UNION ALL
SELECT 'C1493','21st Century',' ','40500 ','P-FS: Force & Strain Sensors' UNION ALL
SELECT 'C1493','21st Century',' ','40850 ','P-HW: Haenni Wheel Load Scales' UNION ALL
SELECT 'C1493','21st Century',' ','40900 ','P-OT: PI - Other Products/Svcs' UNION ALL
SELECT 'C1493','21st Century',' ','50100 ','G-CG: Cold Glue' UNION ALL
SELECT 'C1493','21st Century',' ','50150 ','G-HM: Hot Melt' UNION ALL
SELECT 'C1493','21st Century',' ','50200 ','G-CQ: Gluing Controller & QA' UNION ALL
SELECT 'C1493','21st Century',' ','50910 ','G-SE: GS - Services' UNION ALL
SELECT 'C1493','21st Century',' ','50920 ','G-OT: GS - Other Products' UNION ALL
SELECT 'C1493','21st Century',' ','90100 ','COMA: Contract Manufacturing'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF

SELECT v.*
FROM #MyTable v
ORDER BY PL
Go to Top of Page

Bau_JJones
Starting Member

13 Posts

Posted - 2014-06-27 : 08:14:25
Ah, no 'edit' option on the posts.

I'll now look into how to join that result with the sales_history table.
Go to Top of Page

Bau_JJones
Starting Member

13 Posts

Posted - 2014-06-27 : 11:44:02
I think I'm getting a better idea what the problem is with this case. I've been trying to combine the two results using a CTE, and I'm running into the same problem.

In a nutshell, the problem seems to be that I need the following result:

  • 41 product lines (which is not the complete set; there are obsolete rows in the table), plus descriptions from the prod_lines table

  • all rows from the sales_history table where the prod_line fields match, leaving either NULL or duplicating the cust_name field otherwise. This result requires limitation to the appropriate subset as there are several different groups of data stored in the same table

  • the salesgroup_ID field from the Customers table where the cust_ID field matches the same in the sales_history table



So if I were to select the three tables individually, I would do as below.

For the Product Lines:


SELECT PL.pl_prodline_ID,PL.pl_shortdesc --Select only Product Line and Description columns
FROM prod_lines PL
WHERE PL.pl_prodline_ID>'10000' AND PL.pl_prodline_ID<'91000' --Limit to current product line codes


Then the Sales History information:


SELECT *
FROM sales_history SH
WHERE SH.sah_year='2014' --Limit to this fiscal year
AND SH.sah_sort_id='Z37' --The sort key to limit to the appropriate records
AND SH.sah_sortkey2='C1493' --Limits to 21st Century account to reduce results for testing


And finally the Industry Code from the Customers table:


SELECT cu_salesgroup_ID
FROM customers CU


No matter how I try to join these tables, I end up with one of two results:

[list]
  • All rows from the sales_history table result multiplied by the prodline_ID field in the prod_lines table

  • Limitation to only the two rows from 2014 in the sales_history table for 21st century, including the lined information from the prod_lines table


  • I'm not sure what to do at this point, but I'd love to learn what I am doing wrong. It's got to be something very simple that is just beyond my knowledge at this point.
    Go to Top of Page

    Bau_JJones
    Starting Member

    13 Posts

    Posted - 2014-06-27 : 12:09:07
    Okay, now I'm getting somewhere. This process has been very enlightening thus far.

    This has produced nearly the result I'm looking for:


    --Create a CTE to select all appropriate sales_history rows and the salesgroup_ID field from Customers.
    WITH SH_Cust
    AS
    (SELECT SH.*, CU.cu_salesgroup_ID
    FROM sales_history SH
    RIGHT JOIN customers CU ON SH.sah_sortkey2=CU.cu_cust_id
    WHERE SH.sah_year='2014'
    AND SH.sah_sort_id='Z37'
    AND SH.sah_sortkey2='C1493')

    --Select all the active product line codes from the prod_lines table and join with the CTE from above.
    SELECT PL.pl_prodline_ID,PL.pl_short_desc,SH_Cust.*
    FROM prod_lines PL
    LEFT JOIN SH_Cust
    ON pl_prodline_id=SH_Cust.sah_sortkey3
    WHERE PL.pl_prodline_ID>'10000' AND PL.pl_prodline_ID<'91000'


    Now I just need to fill in the NULL values in the sah_sortkey2_desc field with the result from cust_ID and come up with a sum of the twelve monthly columns to create one yearly result, and I'm done.
    Go to Top of Page

    Bau_JJones
    Starting Member

    13 Posts

    Posted - 2014-06-27 : 13:53:31
    All right, I am once again stumped.

    This does not produce the desired result with more than one account listed, because the result places accounts in available rows of the original 41 rather than duplicating it.

    Now I'm trying to find a way to create a result like this:


    PL1 Customer1
    PL2 Customer1
    PL3 Customer1
    PL4 Customer1
    PL5 Customer1
    PL1 Customer2
    PL2 Customer2
    PL3 Customer2
    PL4 Customer2
    PL5 Customer2


    Even though the data in the sales history table might only list this:


    PL1 NULL
    PL2 Customer1
    PL3 NULL
    PL4 NULL
    PL5 Customer1
    PL1 Customer2
    PL2 NULL
    PL3 Customer2
    PL4 NULL
    PL5 Customer2


    It seems to me that what I need is a way to list all product lines, then reset and list them again, times the number of customers with the customer name listed beside the product line each time. Then I need to show the matching data from the sales history table or a NULL value if there is nothing available.

    I need to rest my head and regroup.
    Go to Top of Page

    Bau_JJones
    Starting Member

    13 Posts

    Posted - 2014-06-30 : 11:15:27
    After doing some research on this I am no closer to a solution.

    My problem seems to be that I cannot even frame the correction properly to find the solution.

    I feel like I might be on the right track with INSERT INTO but I'm uncertain.

    After reading quite a bit, I am thinking that I might need to do something like this:


    • SELECT all the needed data from the three tables.

    • CREATE VIEW to make a virtual table that I can populate as needed.

    • Make every row in the Customers table repeat (if necessary) so that there are 41 rows for every customer name
      [*]INSERT INTO to fill the view with the 41 rows from the Customers table, matching the data from the 41 rows from the Product Lines table (INNER JOIN), and including the applicable rows from the Sales History Table (LEFT OUTER JOIN)



    If I can do all of that, I think I'll have what I need.

    Am I on the right track with that procedure, or am I overlooking something much simpler?
    Go to Top of Page

    Bau_JJones
    Starting Member

    13 Posts

    Posted - 2014-06-30 : 11:18:04
    That procedure should read like this:


    • SELECT all the needed data from the three tables.

    • CREATE VIEW to make a virtual table that I can populate as needed.

    • Make every row in the Customers table repeat (if necessary) so that there are 41 rows for every customer name

    • INSERT INTO to fill the view with the 41 rows from the Customers table, matching the data from the 41 rows from the Product Lines table (INNER JOIN), and including the applicable rows from the Sales History Table (LEFT OUTER JOIN)

    Go to Top of Page

    Bau_JJones
    Starting Member

    13 Posts

    Posted - 2014-07-02 : 17:39:43
    I am slowly starting to get somewhere with this case, but I'm not there yet.

    I broke it into individual steps as listed above, and first came up with a SELECT statement that combines the customers and prod_lines tables (I am using only one account at this time):


    SELECT CU.cu_cust_id AS 'Acct',CU.cu_name AS 'Customer',CU.cu_salesgroup_id AS 'Industry Code',
    PL.pl_prodline_ID as 'Prod Line',PL.pl_short_desc AS 'Description'
    FROM sisl_data02.dbo.customers CU
    CROSS JOIN sisl_data02.dbo.prod_lines PL
    WHERE
    CU.cu_cust_id='C1493' AND
    PL.pl_prodline_id>'10000' AND
    PL.pl_prodline_ID<'91000'


    That code generates 41 rows containing exactly the information I need after completing the first three steps of my procedure.

    So I put this statement into a CTE and tried JOINING it (using every JOIN type) in an attempt to get 41 rows that contain linked records from the sales_history table only where the customer ID and the product line are equal.

    Here is the code:


    --Create a CTE to select all appropriate Customer and Prod Line rows and combine them.
    WITH Cust_PL
    AS
    (SELECT CU.cu_cust_id AS 'Acct',CU.cu_name AS 'Customer',CU.cu_salesgroup_id AS 'Industry Code',
    PL.pl_prodline_ID as 'Prod Line',PL.pl_short_desc AS 'Description'
    FROM sisl_data02.dbo.customers CU
    CROSS JOIN sisl_data02.dbo.prod_lines PL
    WHERE
    CU.cu_cust_id='C1493' AND
    PL.pl_prodline_id>'10000' AND
    PL.pl_prodline_ID<'91000')

    --Select the Sales History rows and match to the Cust_PL CTE.
    SELECT Cust_PL.*,SH.sah_sortkey2,SH.sah_sortkey2_desc AS 'Customer',SH.sah_sortkey3,
    ROUND(SH.sah_sales_pd1_amt+SH.sah_sales_pd2_amt+SH.sah_sales_pd3_amt+
    SH.sah_sales_pd4_amt+SH.sah_sales_pd5_amt+SH.sah_sales_pd6_amt+
    SH.sah_sales_pd7_amt+SH.sah_sales_pd8_amt+SH.sah_sales_pd9_amt+
    SH.sah_sales_pd10_amt+SH.sah_sales_pd11_amt+SH.sah_sales_pd12_amt,0)
    AS 'Sales YTD'
    FROM sisl_data02.dbo.sales_history SH
    Right JOIN Cust_PL
    ON SH.sah_sortkey2=Cust_PL.Acct
    WHERE SH.sah_year='2014' AND
    SH.sah_sort_id='Z37'
    ORDER BY Cust_PL.Customer,Cust_PL.[Prod Line]


    This code results in 123 rows. There are three rows in the sales_history table for customer C1493. What I'm trying to get is 41 rows with the three rows from sales_history listed only in the row where they match.

    If I do the four types of JOINS in the second statement, these are the resulting number of rows:
    • INNER: 123 rows
    • LEFT OUTER: 1773 rows (which makes sense, since it is showing all rows from the sales_history table)
    • RIGHT OUTER: 123 rows (this is confusing because it is the same as the INNER JOIN)
    • CROSS JOIN: 67773 rows (again this makes sense)


    I think I'm doing something wrong in the JOIN criteria and the WHERE clause.

    I have a bit of a problem because the sales_history table has many different sort_id types, for some 57 different reports, and I need to select just one type ('Z37') and one fiscal year ('2014').

    I'm not sure how to do this at this point. It seems to me that I need to select the appropriate rows from sales_history before JOINING the two tables, but I'm not sure how to do that.

    Can someone please give me a nudge in the right direction?

    Thank you.
    Go to Top of Page

    Bau_JJones
    Starting Member

    13 Posts

    Posted - 2014-07-03 : 14:30:55
    All right, I've figure it out. The problem ended up being in the WHERE clause; I should have just included all those criteria in the ON phrase of the JOIN.

    Here is the final code which works:

    --Create a CTE to select all appropriate Customer and Prod Line rows and combine them.
    WITH Cust_PL
    AS
    (SELECT CU.cu_cust_id AS 'Acct',CU.cu_name AS 'Customer',CU.cu_defaultslsrep_id AS 'Territory',
    CU.cu_salesgroup_id AS 'Industry Code',PL.pl_prodline_ID as 'Prod Line',
    PL.pl_short_desc AS 'Description'
    FROM sisl_data02.dbo.customers CU
    CROSS JOIN sisl_data02.dbo.prod_lines PL
    WHERE
    --(CU.cu_cust_id='C1493' OR CU.cu_cust_id='C1492') AND
    PL.pl_prodline_id>'10000' AND
    PL.pl_prodline_ID<'91000')

    --Select the Sales History rows and match to the Cust_PL CTE.
    SELECT Cust_PL.*,--ISNULL(SH.sah_sortkey2,'') AS 'Customer',ISNULL(SH.sah_sortkey2_desc,'') AS 'Name',
    --ISNULL(SH.sah_sortkey3,'') AS 'SortKey3',
    ISNULL(ROUND(SH.sah_sales_pd1_amt+SH.sah_sales_pd2_amt+SH.sah_sales_pd3_amt+
    SH.sah_sales_pd4_amt+SH.sah_sales_pd5_amt+SH.sah_sales_pd6_amt+
    SH.sah_sales_pd7_amt+SH.sah_sales_pd8_amt+SH.sah_sales_pd9_amt+
    SH.sah_sales_pd10_amt+SH.sah_sales_pd11_amt+SH.sah_sales_pd12_amt,0),'')
    AS 'Sales YTD'
    FROM Cust_PL
    LEFT JOIN sisl_data02.dbo.sales_history SH
    ON Cust_PL.Acct=SH.sah_sortkey2
    AND Cust_PL.[Prod Line]=SH.sah_sortkey3
    AND SH.sah_year='2014'
    AND SH.sah_sort_id='Z37'
    ORDER BY Cust_PL.Customer,Cust_PL.[Prod Line]


    Thanks for getting me started on this. I learned quite a bit in the last week, and all said and done it took me about 10 hours to get where I needed to go. A long time for one query, but I picked up some great knowledge that will be invaluable going started.
    Go to Top of Page
       

    - Advertisement -