Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Afer Join, I have more records


I have a table with 211 records, but when I join that with another table (a  
LEFT OUTER JOIN) I get 222 records. Does anyone why this is?

appreciate your help!
Thanks

>I have a table with 211 records, but when I join that with another table (a
> LEFT OUTER JOIN) I get 222 records. Does anyone why this is?

We could guess, but we'd probably have a better go at it if you actually
post you queries.

-----------------------------------------------Reply-----------------------------------------------
Not understanding your question.  You're concerned because LEFT JOIN returns
more rows than you have in one of your tables?  Like the sample below?  The
SmallTable contains only 1 row, the LargeTable contains 6.  The result of
the SELECT with the LEFT JOIN is 6 rows.

CREATE TABLE #SmallTable (id INT NOT NULL PRIMARY KEY)
CREATE TABLE #LargeTable (id INT NOT NULL PRIMARY KEY)

INSERT INTO #SmallTable (id)
VALUES (1)

INSERT INTO #LargeTable (id)
VALUES (1)

INSERT INTO #LargeTable (id)
VALUES (2)

INSERT INTO #LargeTable (id)
VALUES (3)

INSERT INTO #LargeTable (id)
VALUES (4)

INSERT INTO #LargeTable (id)
VALUES (5)

INSERT INTO #LargeTable (id)
VALUES (6)

GO

SELECT *
FROM #LargeTable l
LEFT OUTER JOIN #SmallTable s
ON l.id = s.id

DROP TABLE #SmallTable
DROP TABLE #LargeTable

"Chanaka" <Chan@discussions.microsoft.com> wrote in message

news:21ACD379-1C2B-4732-9357-C52D634B1CC5@microsoft.com...

here's the query

SELECT     dbo.tbcode.code, dbo.users.userid, dbo.users.lname
FROM         dbo.tbcode LEFT OUTER JOIN
                      dbo.users ON dbo.tbcode.lname = dbo.users.lname

"Aaron Bertrand [SQL Server MVP]" wrote:

yeah the left join returns more rows than whats already on the table. here is
the query:

SELECT     dbo.tbcode.code, dbo.users.userid, dbo.users.lname
FROM         dbo.tbcode LEFT OUTER JOIN
                      dbo.users ON dbo.tbcode.lname = dbo.users.lname

thanks!

It's going to return ALL the rows in tbcode and then for rows that the join
matches in users, it will show userid and lname, else they will be NULL.

Can you tell us:

SELECT COUNT(*) FROM dbo.tbcode;

SELECT COUNT(*) FROM dbo.users;

SELECT COUNT(*) FROM dbo.tbcode t INNER JOIN dbo.users u
ON t.lname = u.lname;

Also, it sounds like you are relying on last name only, as your unique
identifier for a user?  That isn't very healthy.

"Chanaka" <Chan@discussions.microsoft.com> wrote in message

news:581A6BE7-368F-4809-A9A2-DF489CB859D1@microsoft.com...

Run this:

SELECT COUNT(*) FROM dbo.tbcode
SELECT COUNT(*) FROM dbo.users
SELECT COUNT(DISTINCT lname)
FROM dbo.tbcode
SELECT COUNT(DISTINCT lname)
FROM dbo.users

What do you get back?

"Chanaka" <Chan@discussions.microsoft.com> wrote in message

news:21E8A84B-6FCF-4D5D-9FBA-05099E1B9755@microsoft.com...

> yeah the left join returns more rows than whats already on the table.

I'm not sure why you're surprised by this.  Take the following example, and
tell us how ad why you think the output should contain 3 rows or less?

CREATE TABLE #foo
(
 code INT,
 lname VARCHAR(32)
);

CREATE TABLE #bar
(
 userid INT,
 lname VARCHAR(32)
);

SET NOCOUNT ON;

INSERT #foo SELECT 1, 'bertrand';
INSERT #foo SELECT 2, 'chanaka';
INSERT #foo SELECT 3, 'gates';

INSERT #bar SELECT 10, 'bertrand';
INSERT #bar SELECT 11, 'gates';
INSERT #bar SELECT 12, 'bertrand';

SELECT t.code, u.userid, u.lname
FROM #foo t
LEFT OUTER JOIN #bar u
ON t.lname = u.lname;

DROP TABLE #foo, #bar;

-----------------------------------------------Reply-----------------------------------------------

Yeah I know its not healthy, I am trying to migrate data from a POS database
which wasn't designed by me to a new database. lol
Here are the results:

SELECT COUNT(*) FROM dbo.tbcode; = 221

SELECT COUNT(*) FROM dbo.users;= 318

SELECT COUNT(*) FROM dbo.tbcode t INNER JOIN dbo.users u ON t.lname =
u.lname; = 175

"Aaron Bertrand [SQL Server MVP]" wrote:

"Aaron Bertrand [SQL Server MVP]" <ten.@dnartreb.noraa> wrote in message
news:ON4TvI8SHHA.2124@TK2MSFTNGP06.phx.gbl...

>> yeah the left join returns more rows than whats already on the table.

> I'm not sure why you're surprised by this.  Take the following example,
> and tell us how ad why you think the output should contain 3 rows or less?

I'm starting to suspect Chanaka might need to explore the wonders of INNER
JOIN...

-----------------------------------------------Reply-----------------------------------------------
SELECT COUNT(*) FROM dbo.tbcode = 221
SELECT COUNT(*) FROM dbo.users = 318
SELECT COUNT(DISTINCT lname)  FROM dbo.tbcode = 108
SELECT COUNT(DISTINCT lname) FROM dbo.users = 297

thanks

While you're at it, how many NULL lnames in each table?

SELECT COUNT(*) FROM dbo.tbcode WHERE lname IS NULL
SELECT COUNT(*) FROM dbo.users WHERE lname IS NULL

"Chanaka" <Chan@discussions.microsoft.com> wrote in message

news:319DE614-ECF5-40DF-9397-66DD0C430A99@microsoft.com...

it can't becuase #bar doesn't have a record for a person called "Chanaka".

That means some of the LASTNAMES in the Code table exist in the users table.

Thank you for helping me figure this out!

"Aaron Bertrand [SQL Server MVP]" wrote:

right! lol
thanks guys

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc