• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1062
  • Last Modified:

LEFT OUTER JOIN returns DUPLICATE ROWS. please help.

Hi,

I have the following SQL which uses inner joins and left outer join.

I am expecting that the LEFT OUTER JOIN should return NULLs for duplicate values.
But I am getting duplicate rows for the last 3 columns (A.CONT_CLIENT_ID, F.CUST_NAME, CASE A.TYPE_CD WHEN 1 THEN  'CONTCTR' WHEN 2 THEN  'CLIENT') -

Can you please help and tell what I am doing wrong?

Here is the query:

SELECT C.CUST_ID, C.CUST_NAME, D.CUST_LNAME, D.CUST_FNAME, A.CONT_CLIENT_ID
, F.CUST_NAME,
CASE A.TYPE_CD
WHEN 1 THEN  'CONTCTR'
WHEN 2 THEN  'CLIENT'
END
FROM
DTEST.TS_CUST_ENT C
INNER JOIN DTEST.TS_EXCL_CUST E
ON E.CUST_ID = C.CUST_ID AND C.REGYEAR = E.REGYEAR

INNER JOIN DTEST.TS_CUST D ON E.CUST_ID = D.CUST_ID  AND E.REGYEAR = D.REGYEAR

LEFT OUTER JOIN
(SELECT CUST_ID, CONT_CLIENT_ID, REGYEAR, TYPE_CD FROM DTEST.TS_CONTCTR_CLIENT
 GROUP BY CUST_ID, CONT_CLIENT_ID,REGYEAR, TYPE_CD) AS A
ON A.CUST_ID = C.CUST_ID AND A.REGYEAR = C.REGYEAR

INNER JOIN DTEST.TS_CUST_ENT F ON  
A.CONT_CLIENT_ID = F.CUST_ID
AND (F.REGYEAR = 2011)

WHERE
A.REGYEAR = 2011 AND A.TYPE_CD IN (1, 2)
AND C.DATE_TERMINATED = 0

ORDER BY C.CUST_ID
FOR FETCH ONLY

I really appreciate your help.
Thanks.
0
LoveSpring
Asked:
LoveSpring
  • 3
  • 2
  • 2
2 Solutions
 
momi_sabagCommented:
it is a bit hard to understand exactly what you need, so i will try to provide a general explanation
let's say you have an orders table with order_id and an items table with order id, and you want to left outer join, and have null for the columns of the order_items columns for orders that have more than one item with the same isbn (duplicates)
so you will have something like:

select *
from  orders t1
  left outer join (select *, count(*) over(partition by order_id, isbn) items_in_group
                           from order_items) t2
      on t1.order_id = t2.order_id and t2.items_in_group = 1
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi LoveSpring,

Why do you expect nulls for duplicate values?  You may want nulls, but nothing in your query suggests that it will generate nulls.

When the filter query is applied, derived table A should produce at most 2 rows per cust_id/cont_client_id combination.  All rows for years other than 2011 will be eliminated.  The subquery (that generates A) essentially does a DISTINCT over the 4 returned fields.  The number of rows generated is indeterminate from the SQL, but should be reduced to no more than 2 rows per cust_id/cont_client_id in the final filtering.

Which means that there is a cartesian in the joining of tables ts_cust_ent, ts_excl_cust, and ts_cust.  Or there is a cartesian when ts_cust_ent is joined again in the final step.

We know that there can be two rows per cust_id/cont_client_id combination (one for each type_cd value).  So when the last step joins ts_cust_ent back to the prior results, you may get two rows because one of the join keys isn't the type_cd.

Try including the type_cd in the join key, or skipping the last join.


Good Luck,
Kent


SELECT 
  C.CUST_ID, C.CUST_NAME, D.CUST_LNAME, D.CUST_FNAME,
  A.CONT_CLIENT_ID, F.CUST_NAME,
  CASE A.TYPE_CD
    WHEN 1 THEN  'CONTCTR'
    WHEN 2 THEN  'CLIENT'
  END
FROM DTEST.TS_CUST_ENT C
INNER JOIN DTEST.TS_EXCL_CUST E
   ON E.CUST_ID = C.CUST_ID 
  AND C.REGYEAR = E.REGYEAR
INNER JOIN DTEST.TS_CUST D 
   ON E.CUST_ID = D.CUST_ID  
  AND E.REGYEAR = D.REGYEAR
LEFT OUTER JOIN
(
  SELECT CUST_ID, CONT_CLIENT_ID, REGYEAR, TYPE_CD 
  FROM DTEST.TS_CONTCTR_CLIENT
  GROUP BY CUST_ID, CONT_CLIENT_ID,REGYEAR, TYPE_CD
) AS A
   ON A.CUST_ID = C.CUST_ID AND A.REGYEAR = C.REGYEAR
INNER JOIN DTEST.TS_CUST_ENT F 
   ON A.CONT_CLIENT_ID = F.CUST_ID
  AND (F.REGYEAR = 2011)
WHERE A.REGYEAR = 2011 
  AND A.TYPE_CD IN (1, 2)
  AND C.DATE_TERMINATED = 0
ORDER BY C.CUST_ID
FOR FETCH ONLY

Open in new window

0
 
LoveSpringAuthor Commented:
Hi Kdo,

Can you please tell me how to code to display NULLS when duplicates found for the last 3 columns in the above select statement?

Thanks.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Kent OlsenData Warehouse Architect / DBACommented:
Hmm.....

That's not a trivial change, nor a trivial query.

You may be able to use one of the OLAP extensions.

Join the tables, then do a GROUP BY CUBE (column_list....).  DB will do the rest.


Kent
0
 
LoveSpringAuthor Commented:
if I add another layer of SELECT, as shown below, can I change duplicate cell values to spaces?

SELECT
T2.CUST_ID, T2.CUST_NAME, T2.CUST_LNAME, T2.CUST_FNAME,
  T2.CONT_CLIENT_ID, T2.CUST_NAME, T2.TYPE_CD
 
FROM
(
SELECT
  C.CUST_ID, C.CUST_NAME, D.CUST_LNAME, D.CUST_FNAME,
  A.CONT_CLIENT_ID, F.CUST_NAME,
  CASE A.TYPE_CD
    WHEN 1 THEN  'CONTCTR'
    WHEN 2 THEN  'CLIENT'
  END
FROM DTEST.TS_CUST_ENT C
INNER JOIN DTEST.TS_EXCL_CUST E

---
---
) AS T2


Thanks.

0
 
momi_sabagCommented:
can you post here the query that you are using?
you can't use an outer query since you can't know in the outer query if the nulls were caused  by duplicate records or records that were not found at all
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi LoveSpring,

The "issue", if you will, is that the SQL autonomous groupings are cell, row, and result set.  There isn't really a function to "do this for row x but do this other thing for row y".

That said, you CAN mimic the feature by wrapping the query with one that produces a row number, grouped by your key.


It adds some work to the query, but if the result set is fairly small it's probably not an issue.


Kent


SELECT  
--  main results
  case when RN = 1 THEN CONT_CLIENT_ID else NULL END,
  case when RN = 1 THEN CUST_NAME else NULL END,
  case when RN = 1 THEN TYPE_CD else NULL END
FROM
(
  SELECT row_number () over (group by CONT_CLIENT_ID, CUST_NAME) as RN, t0.*
  FROM
  (
  -- your query here
  ) t0
) t2

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now