left join - 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

left join

Author

Topic

anjali66
Starting Member

23 Posts

Posted - 2011-01-11 : 13:09:52

Hello Everyone,

I have two tables. One table Table A has 44261 records and another table table B has 48357 records. I wrote a query so that I did the left join

so I did

Insert INTO table A
( Col1,
col2,
col3,
col4,
col5)

select

b.col1,
b.col2,
b.col3,
b.col4,
b.col5

from table B b LEFT JOIN table A
ON b.ID = A.ID

I ended up with 92618 records. I am not sure what am I doing wrong because with left join, it shoukd be more than 48357 records in Table A

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-11 : 13:20:28

There is likely another link than just ID.

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-11 : 13:37:59

The query did exactly what you told it to do - Insert all records from table B. That's where constraints help to prevent these sorts of things. :)

Do you need a WHERE clause? (WHERE A.ID IS NULL)?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-11 : 22:36:33

My guess:
It may be the case of Many to many relationship.
TableA and TableB both don't have Unique ID.

It will be better if you can post some sample data.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-12 : 01:59:38

If the query has executed without any error then i think there was already some information in the Table A. As in this case the number of records (result set of the select query) should not exceed the total number of records in Table B, which is 48357.

try to remove all records from the table into which you are inserting data. e.g.

Truncate table tableName
Insert INTO TableName
( Col1,col2,col3,col4,col5)

select
b.col1,b.col2,b.col3,b.col4,b.col5

from table B b LEFT JOIN table A
ON b.ID = A.ID

With the above you should end up with the total number of rows equal to the Rows in "Table B"

Cheers!
MIK

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-12 : 22:18:47

quote:
Originally posted by MIK_2008

If the query has executed without any error then i think there was already some information in the Table A. As in this case the number of records (result set of the select query) should not exceed the total number of records in Table B, which is 48357.

try to remove all records from the table into which you are inserting data. e.g.

Truncate table tableName
Insert INTO TableName
( Col1,col2,col3,col4,col5)

select
b.col1,b.col2,b.col3,b.col4,b.col5

from table B b LEFT JOIN table A
ON b.ID = A.ID

With the above you should end up with the total number of rows equal to the Rows in "Table B"

Cheers!
MIK

Hi Mik,

Nowhere OP has mentioned that ID is unique in one of the two tables.
Assume that ID in tableA is as follows:
1
2
2
3
4
4
5

ID in tableB is as follows:
1
2
2
2
3
3
4
4
5

As per your explanation the total number of records returned should be 9 but you can check it will give more.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-13 : 00:16:19

hi pk_bohra,

yup i went with the assumption of ID to be unique!~ :)

Any how this doubt can be eliminated by using "Distinct"

Cheers!
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources