Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to get the join to work ?

Posted on 2013-01-04
3
343 Views
Last Modified: 2013-01-13
Hi!

Have this sql-code:

      SELECT distinct
           CUST.ABC_FORECASTGROUPID,
           FORCAST.DESCRIPTION,
           SALE.ITEMID,
           sum(SALE.QTYORDERED),
           DATACELLS.cellvalue,
           cast(year(SALE.CONFIRMEDDLV) AS varchar(4)) + cast(DATEPART(week,SALE.CONFIRMEDDLV) as varchar(2))
        FROM
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[SALESLINE] AS SALE
        INNER JOIN
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE] AS CUST
        ON
            (
                 ((SALE.CUSTACCOUNT = CUST.ACCOUNTNUM) AND
                 (SALE.DATAAREAID  COLLATE DATABASE_DEFAULT = CUST.DATAAREAID  COLLATE DATABASE_DEFAULT))
            )
        INNER JOIN
            [hgf-sql01].[bizview_hfs].[dat].[data_cells] AS DATACELLS
        ON
            (
                ((CUST.ABC_FORECASTGROUPID COLLATE DATABASE_DEFAULT = DATACELLS.ws_name COLLATE DATABASE_DEFAULT) OR
                (CUST.ABC_FORECASTGROUPID=' ')) AND (DATACELLS.version_id='PU4012') AND (DATACELLS.valtype_id='ANT') AND
                (DATACELLS.timeunit_id='ISOWEEK') AND (DATACELLS.timeval_id=cast(year(SALE.CONFIRMEDDLV) AS varchar(4)) + cast(DATEPART(week,SALE.CONFIRMEDDLV) as varchar(2)))
            )
            
        INNER JOIN
            [hgf-sql01].[bizview_hfs].[dat].[data_dimensions] AS DIMENTIONS
        ON
            (
                ((DATACELLS.dim_cluster_id  = DIMENTIONS.dim_cluster_id) AND
                (DIMENTIONS.obj_id='item') AND (DATACELLS.client_id  = DIMENTIONS.client_id))
                  
                   
            ) 
            
        INNER JOIN
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[ABC_FORECASTGROUP] AS FORCAST
        ON
            (
                 ((FORCAST.FORECASTGROUPID = CUST.ABC_FORECASTGROUPID) AND
                 (FORCAST.DATAAREAID = CUST.DATAAREAID))
                  
                   
            ) 
 
group by
    CUST.ABC_FORECASTGROUPID,
    FORCAST.DESCRIPTION,
    SALE.ITEMID,
    DATACELLS.cellvalue,
    cast(year(SALE.CONFIRMEDDLV) AS varchar(4)) + cast(DATEPART(week,SALE.CONFIRMEDDLV) as varchar(2))

Open in new window


When i run this query, it takes about 25 minits to complete ?

 [hgf-sql01].[hfgh-axdb5-drift].[DBO].[SALESLINE] AS SALE
-> contains of 1039137 records

   [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE] AS CUST
-> contains of 18045 records

But the strange ting is that query
always return more then 1 million records ???

Tryed to change this lines

       FROM
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[SALESLINE] AS SALE
        INNER JOIN
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE] AS CUST

Open in new window


TO:
       FROM
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[SALESLINE] AS SALE
       LEFT JOIN
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE] AS CUST

Open in new window


OR:
       FROM
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[SALESLINE] AS SALE
       right JOIN
            [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE] AS CUST

Open in new window



But dosent help....

I acnt return more records then the table:
 [hgf-sql01].[hfgh-axdb5-drift].[DBO].[CUSTTABLE]


What is wrong here ?
0
Comment
Question by:team2005
3 Comments
 
LVL 4

Expert Comment

by:krtyknmsql
ID: 38743262
It is very hard to tell without the execution plan. Can you please let me know the index details for the tables SALESLINE and CUSTTABLE?

Is CUSTACCOUNT, ACCOUNTNUM and DATAAREAID are indexed columns?
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 38744274
It means that your INNER JOINs are yielding duplicates. Maybe you do not have the correct fields involved in the ON clause. In other words, check if multiple columns combine to form the unique identifier for the matches.

Note: if you use a LEFT JOIN from the SALESLINE table, you will get 1,039,137 (i.e., over a million) rows. Therefore, if your intent is to get all the CUSTTABLE records and only their matches, then use INNER JOIN or make CUSTTABLE the left-most table in the LEFT JOIN. If you get more rows than expected, then resolve the duplicates.

As a final thought, the issue may be that the duplicates are appropriate. A customer should have multiple matches in a sales order table, especially sales order lines—one sales order can have multiple items or delivery lines. Therefore, what you may need is a derived query to aggregate SALESLINE data by customer.

For example:
FROM CUSTTABLE CUST
/* INNER JOIN to return only customers with sales; OUTER JOIN for all customers. */
LEFT JOIN (
    SELECT  CUSTACCOUNT, sum(SALE.QTYORDERED)
    FROM SALESLINE
    GROUP BY CUSTACCOUNT
) SALE(CUSTACCOUNT, QTYORDERED) ON SALE.CUSTACCOUNT = CUST.ACCOUNTNUM

Open in new window


You can make this by customer, by year, by item. Just note that the number of rows will be more than what is in the customer table by the amount of variability in sales. The more items per customer, the more years per customer, et cetera, the more lines per customer in the final SELECT.

I hope that makes sense.

Best regards and happy coding,

Kevin
0
 
LVL 2

Author Closing Comment

by:team2005
ID: 38772819
thanks
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
t-sql need help on t-sql 10 26
SQL server 2014 replication error 20 38
AD and SQL Server 2016 2 29
install report service in sccm2012 3 19
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

837 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