join 2 tables and avoid duplicates

Question

I am trying to create a temp table in SQL by joining 2 existing temp tables. My tables look like:

I'm trying to join the two tables. But if I do a left join, it gives me a lot of duplicate rows and the table looks like this:

As you can see, the data in red is duplicated and only the data in first row is unique. My code is below:

select 

        CMO.CUSIP,
        CMO.LEVEL1,
        CMO.COUPON,
        CMO.TRANCHE_GROUP as 'Tranche Group',
        CMO.TRANCHE_TYPE as 'Tranche Type',     
        CMO.price as 'Current Price',
        CMO.PREV_PRICE as 'Previous Price',
        CMO.price_dec as 'Color',
        cast (CMO.DataDate as date) as 'Color Date',
        CMO.dealer as 'Dealer',
        CHA.Challenge_Date,
        CHA.Challenge_Price,
        CHA.Updated_Price,
        CHA.Client_Name,
        CHA.Evaluator,
        CHA.Accepted,
        CMO.DATA_DATE

into #cmodatatable
from
        #cmocolortemptable CMO 
        left join #challengetemptbl CHA on CMO.CUSIP = CHA.CUSIP1 

select *
from #cmodatatable

Is there a way to avoid this? I'm trying to get an output like this:

I'm exporting this data into Tableau, and counting the distinct values in Tableau is becoming very tedious, that's why I'm trying to do it in SQL and then take the data to Tableau. I think I might have to do a union but since the data is different in the tables, I don't know how I'll do a union. Any help is appreciated in this regard. Thank you!

If the entire row is duplicated you can use `SELECT DISTINCT`. Otherwise you'll have to start grouping. — Explosion Pills, Mar 26 '18 at 16:29
All rows are unique. Specify the expected result! (No images please, use formatted text.) Before you spend too much time, take a look at https://stackoverflow.com/help/mcve. — jarlh, Mar 26 '18 at 16:32
I mean, you are joining a table that has multiple rows per cusip with one that has only one row per cusip....the result is the expected one, with data repeated from the second table, since it's the same cusip. What would be your desired result? — Lamak, Mar 26 '18 at 16:36
@lamak, this is just an example cusip, my table has a lot of other cusips that have much more data, but I tried to post a simple example here. I'm trying to get a table that has separate rows for the 2 tables. You can see this in the last image I've posted. — frisbeee, Mar 26 '18 at 16:40
Seems that your data are not duplicated in each row you have different color date — ScaisEdge, Mar 26 '18 at 16:42
@frisbeee, 1. there are no duplicates in your result as the first table's data is different. 2. if you do not want the second table column to be populated against the first table data, you should not use join. UNION ALL would help you if you want the nulls to be shown for columns of other table. — Avani, Mar 26 '18 at 16:59
@avani, i understand that there are no duplicates from the first row, but because there is only one row in the second table for that cusip, the data from second table gets duplicated to fill in rows 2, 3 and 4 (since table 1 has 4 rows). That is what I'm trying to avoid. I thought of union too, but doesn't union all need the same number of columns in both tables and same data type? — frisbeee, Mar 26 '18 at 17:20

score 0 · Answer 1 · edited Mar 26 '18 at 17:11

0

You need to bring about uniqueness in the first table by including the row_number in both the table and then join them:

sample :

CREATE TABLE #TEMP1(ID INT,NAME VARCHAR(10),AGE INT ,SEX VARCHAR(10))

CREATE TABLE #TEMP2(ID INT,COMP VARCHAR(10))

INSERT INTO #TEMP1 VALUES(1,'A',23,'M')
INSERT INTO #TEMP1 VALUES(1,'B',33,'M')
INSERT INTO #TEMP1 VALUES(1,'C',43,'M')
INSERT INTO #TEMP1 VALUES(1,'D',53,'M')

INSERT INTO #TEMP2 VALUES(1,'COMP')

;WITH CTE1 AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY ID) RNUM FROM #TEMP1

)
,CTE2 AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY ID) RNUM FROM #TEMP2)

SELECT *  FROM CTE1 C1 
LEFT OUTER JOIN CTE2 C2
ON C1.RNUM=C2.RNUM

edited Mar 26 '18 at 17:11

Safiyya

1,383
1
10
16

answered Mar 26 '18 at 16:48

sumo

26
3

left outer join on rownumber would have 1st row with data of two tables but as per his requirement, it feels like he wanted nulls to be shown for all the rows in first table for the columns of second table – Avani Mar 26 '18 at 16:56
https://stackoverflow.com/questions/4027212/how-to-combine-results-from-multiple-tables-with-different-columns This is exactly what I need, but I didn't understand the " `charges` ON (0) LEFT OUTER JOIN `day` ON (0) " part. If someone can help me with that, that'd be awesome! – frisbeee Mar 26 '18 at 17:58

join 2 tables and avoid duplicates

1 Answers1