Join returns duplicate rows - SQL Server Forums

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

Join returns duplicate rows

Author

Topic

Crumbs
Starting Member

7 Posts

Posted - 2007-10-23 : 05:16:01

Hi,
I'm having a little trouble with the following code:

SELECT DISTINCT cd1.*, cd2.* 
FROM Table1 cd1 LEFT JOIN Table2 cd2 
ON cd1.RegNr=cd2.RegNr
WHERE cd1.RegNr = $RegNr

I want it to return the 2 rows that is present in the tables but it returns 4.

126	2007-10-20 10:14:00
126	2007-10-20 10:14:00
126	2007-10-20 10:17:00
126	2007-10-20 10:17:00

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 05:20:46

presumably there are multiple rows in Table2 with RegNr matching Table1.

You need to either remove those duplicate rows, or decide WHICH of the duplicate rows you want to JOIN to

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 05:23:57

How come cd1.*, cd2.*
only returns 2 columns?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 05:27:24

$RegNr

Not sure about the $ either - although I suppose this may be dynamic SQL from PHP or Perl or somesuch

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 05:27:45

quote:
Originally posted by Crumbs

Hi,
I'm having a little trouble with the following code:

SELECT DISTINCT cd1.*, cd2.*
FROM Table1 cd1 LEFT JOIN Table2 cd2
ON cd1.RegNr=cd2.RegNr
WHERE cd1.RegNr = $RegNr

I want it to return the 2 rows that is present in the tables but it returns 4.

126 2007-10-20 10:14:00
126 2007-10-20 10:14:00
126 2007-10-20 10:17:00
126 2007-10-20 10:17:00

Can you post the full output and the result you want?

Madhivanan

Failing to plan is Planning to fail

Crumbs
Starting Member

7 Posts

Posted - 2007-10-23 : 05:31:49

The two tables are pretty large, over 100 columns each. That's why the guy who made the table split them in two.

There are only RegNr that's in both tables. And I want to merge the rows in table 1 and table 2 into one long row.

What do you mean with "You need to either remove those duplicate rows, or decide WHICH of the duplicate rows you want to JOIN to"?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 05:35:21

quote:
Originally posted by Crumbs

The two tables are pretty large, over 100 columns each. That's why the guy who made the table split them in two.

There are only RegNr that's in both tables. And I want to merge the rows in table 1 and table 2 into one long row.

What do you mean with "You need to either remove those duplicate rows, or decide WHICH of the duplicate rows you want to JOIN to"?

If there are 100s of columns and there are no duplicates
Duplicates occur only if you select two columns

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 05:35:37

When using DISTINCT, you are putting that statement on ALL columns in the select list!
And SELECT * will get you all columns.

BUT... If you change your select to

SELECT DISTINCT cd1.ID, cd2.Date 
FROM Table1 cd1 LEFT JOIN Table2 cd2 
ON cd1.RegNr = cd2.RegNr
WHERE cd1.RegNr = $RegNr

you will get desired result.

E 12°55'05.25"
N 56°04'39.16"

Crumbs
Starting Member

7 Posts

Posted - 2007-10-23 : 05:38:12

quote:
Originally posted by Peso

When using DISTINCT, you are putting that statement on ALL columns in the select list!
And SELECT * will get you all columns.

BUT... If you change your select to
SELECT DISTINCT cd1.ID, cd2.Date
FROM Table1 cd1 LEFT JOIN Table2 cd2
ON cd1.RegNr = cd2.RegNr
WHERE cd1.RegNr = $RegNr
you will get desired result.

Well, the problem is that there are over 100 columns in each table. Do I have to specify each column like that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 05:41:38

Every single column that is important for the output, yes!
Or * will get you ALL columns.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 05:44:07

and are you using SQL Server?
What is meant by $RegNr?

Madhivanan

Failing to plan is Planning to fail

Crumbs
Starting Member

7 Posts

Posted - 2007-10-23 : 05:49:23

quote:
Originally posted by madhivanan

and are you using SQL Server?
What is meant by $RegNr?

I'm using PHP with SQL Server.
It's a input variable.

Crumbs
Starting Member

7 Posts

Posted - 2007-10-23 : 06:22:44

quote:
Originally posted by Peso

Every single column that is important for the output, yes!
Or * will get you ALL columns.

I've tried both but it returns the same result.
2 duplicate rows

The strange thing is that if there are one row in both tables, it returns one row. but if it's 2 rows, it returns 4...

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 06:32:23

"but if it's 2 rows, it returns 4..."

Sounds like you have 2 rows in EACH table with same RegNr. Every row in Every table that has the same RegNr will be matched. So 2^2 = 4

Sounds like there is an additional column that is needed in the JOIN to match one-to-one

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 06:35:47

"What do you mean with "You need to either remove those duplicate rows, or decide WHICH of the duplicate rows you want to JOIN to"?"

You said:

"And I want to merge the rows in table 1 and table 2 into one long row."

So in order to do that if you have one row in Table1 with a given RegNr, and two rows in Table2 with the same RegNr (just to give an example of what I mean) then you have to decide which of the two rows in Table2 you want to join to. It could be the one with the lowest ID number, most recent date, the first one "using random choice", but it needs to be a single row for what you want to do.

But it sounds to me from the earlier post that the tables need to be joined on more than just RegNr, so this is a moot point, just for explanation of what I was meaning.

What's the PK on Table1 / Table2? That might shed some light on what needs to be joined.

Kristen

Crumbs
Starting Member

7 Posts

Posted - 2007-10-23 : 06:47:05

quote:
Originally posted by Kristen

"What's the PK on Table1 / Table2? That might shed some light on what needs to be joined.

The PK in both tables are RegNr

Table 1:
RegNr
Date
Puls
P_Tid
PQ_Tid
QRS_Tid
QT_Tid
QTc_Tid
P_Axis
QRS_Axis
T_Axis
...and 100 more columns

Table 2
RegNr
Date
Tid_I_I
Tid_I_II
Tid_I_III
Tid_I_aVR
Tid_I_aVL
Tid_I_aVF
... and 100 more columns

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 07:03:39

"The PK in both tables are RegNr"

Then if you join Table1 to Table2 on RegNr it is impossible to get more than one row for a given value of RegNr.

There must be something else you are doing that you have not shown.

What does this give:


SELECT cd1.ID, cd2.Date 
FROM Table1 cd1 
    LEFT JOIN Table2 cd2 
        ON cd1.RegNr = cd2.RegNr
WHERE cd1.RegNr = 126

Kristen

Crumbs
Starting Member

7 Posts

Posted - 2007-10-23 : 07:16:47

I think I solved it.
Since "Date" is in both tables, I made it to work like this.

This worked:

SELECT cd1.*, cd2.* 
FROM Table1 cd1 
LEFT JOIN Table2 cd2
ON cd1.RegNr=cd2.RegNr AND cd1.Date=cd2.Date 
WHERE cd1.RegNr = $RegNr

Thanks everyone for taking your time to try and help me.

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 07:20:29

I'm still bothered that there is an issue at all, so you may not be safe in moving forward with that additional field in the JOIN.

I recommend that you double check that the PK is the single column RegNr - because if it is what you are seeing is not possible, and therefore whatever the reason is that you are seeing it

is not a good reason for moving forwards adding Date as a tie-break.

Up to you though

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources