Unwanted 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

Unwanted duplicate rows

Author

Topic

sinan92
Starting Member

13 Posts

Posted - 2012-01-07 : 11:07:58

Well what I want to do is join another table and the rows of that table need to have the same id as the other so I can add columns of the second table to the query
This is the query:

SELECT c.m_idPlayer, m.m_idPlayer, c.m_szName, m.MultiServer, c.m_nLevel, c.m_nJob, c.m_dwGold, c.TotalPlayTime
FROM CHARACTER_TBL AS c, tblMultiServerInfo AS m
WHERE c.m_chAuthority = 'F'
ORDER BY c.m_nLevel DESC

But I get all rows duplicated a lot of times
It gets duplicated as much times as the amount of rows there are in the table

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-07 : 11:27:53

You are missing a join between the tables.

FROM CHARACTER_TBL AS c INNER JOIN tblMultiServerInfo AS m ON <join condition>

Without that you get a cross join, each row of the first table matching to each and every row of the second table.

--
Gail Shaw
SQL Server MVP

sinan92
Starting Member

13 Posts

Posted - 2012-01-07 : 11:53:15

When I do it like
SELECT c.m_idPlayer, m.m_idPlayer, c.m_szName, m.MultiServer, c.m_nLevel, c.m_nJob, c.m_dwGold, c.TotalPlayTime
FROM CHARACTER_TBL AS c
INNER JOIN tblMultiServerInfo AS m ON
WHERE c.m_chAuthority = 'F' ORDER BY c.m_nLevel DESC

It shows no results at all :/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-07 : 12:12:03

You have to tell SQL what to join on. For example:

SELECT c.m_idPlayer,
       m.m_idPlayer,
       c.m_szName,
       m.MultiServer,
       c.m_nLevel,
       c.m_nJob,
       c.m_dwGold,
       c.TotalPlayTime
FROM   CHARACTER_TBL AS c
       INNER JOIN tblMultiServerInfo AS m
	         ON c.m_idPlayer = m.m_idPlayer
WHERE  c.m_chAuthority = 'F'
ORDER BY
       c.m_nLevel DESC

What that indicates to SQL Server is to take each m_idPlayer value in the CHARACTER_TBL and match it up against row(s) that have the same value in the m_idPlayer column of tblMultiServerInfo.

Now, I don't know if that is the join condition you want to use, but you need something similar based on your requirements.

This short description on MSDN might be useful: http://msdn.microsoft.com/en-us/library/aa213233(v=sql.80).aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 12:12:06

quote:
Originally posted by sinan92

When I do it like
SELECT c.m_idPlayer, m.m_idPlayer, c.m_szName, m.MultiServer, c.m_nLevel, c.m_nJob, c.m_dwGold, c.TotalPlayTime
FROM CHARACTER_TBL AS c
INNER JOIN tblMultiServerInfo AS m ON <missing condition>
WHERE c.m_chAuthority = 'F' ORDER BY c.m_nLevel DESC

It shows no results at all :/

please put the correct condition after On

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sinan92
Starting Member

13 Posts

Posted - 2012-01-07 : 12:27:09

Thats the correct condition but it doesnt work either somehow
I just get no results
This is how I call them
$result = mssql_query($sql);
while($consulta = mssql_fetch_assoc($result)) {
//if the user is a normal user just show their lvl

$lvl = $consulta['m_nLevel'];
$job = $consulta['m_nJob'];
$status = $consulta['MultiServer'];

echo '<td style="padding-left:12px;font-size:13px;"><a name="'.$consulta['c.m_szName'].'">'.$consulta['m_szName'].'</a></td>';
echo '<td style="padding-left:12px;font-size:13px;">'.checkstatus($status).'</td>';
echo '<td style="padding-left:12px;font-size:13px;">'.$lvl.'</td>';
echo '<td style="padding-left:12px;font-size:13px;">'.getjob($job). " " . getmhl($job,$lvl).'</td>';
echo '<td style="padding-left:12px;font-size:13px;">'.$consulta['m_dwGold'].'</td>';
echo '<td style="padding-left:12px;font-size:13px;">'.$consulta['TotalPlayTime'].'</td></tr>';
$rank++;
}
echo "</tr>";
echo "</table></div>";

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-07 : 12:34:03

Check your data then, if it's the right condition and there is data that matches the predicates, you will get results.

--
Gail Shaw
SQL Server MVP

sinan92
Starting Member

13 Posts

Posted - 2012-01-07 : 12:38:59

Well I did they way sunitabeck did it was right but it somehow just doesnt work
While when I leave the second table out it just works fine but without the other column I want
Btw its mssql I dont know if it changes anything

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-07 : 12:44:08

All of what we've said is for MS SQL Server.

--
Gail Shaw
SQL Server MVP

sinan92
Starting Member

13 Posts

Posted - 2012-01-07 : 12:52:09

Oh lol
But meh it just doesnt work it doesnt give any results
This is the query I use
SELECT c.m_idPlayer,
m.m_idPlayer,
c.m_szName,
m.MultiServer,
c.m_nLevel,
c.m_nJob,
c.m_dwGold,
c.TotalPlayTime
FROM CHARACTER_TBL AS c
INNER JOIN tblMultiServerInfo AS m
ON c.m_idPlayer = m.m_idPlayer
WHERE c.m_chAuthority = 'F'
ORDER BY
c.m_nLevel DESC

The tables have both dbo. in front of it but I suppose thats normal. And the ids are the same in both tables but the second table only has id ,serverindex and the MultiServer column in it

sinan92
Starting Member

13 Posts

Posted - 2012-01-07 : 13:40:47

I noticed that one of them was DES and one of them ASC I dont know if that matters also one of them had more rows but I deleted those
Now they all belong to each other and they still dont show up

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-07 : 13:57:10

If that doesn't show any data then you have no matching rows (matching on the m_playerID) that have m_chAuthority = 'F'. There's nothing mystical about SQL queries. Double check that the data really does match (and if you want, post the data here and we can do a sanity check)

--
Gail Shaw
SQL Server MVP

sinan92
Starting Member

13 Posts

Posted - 2012-01-07 : 14:06:14

This is the Multiserver SQL:

USE [CHARACTER_01_DBF]
GO
/****** Object: Table [dbo].[tblMultiServerInfo] Script Date: 01/07/2012 15:04:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblMultiServerInfo](
[m_idPlayer] [char](7) NOT NULL,
[serverindex] [char](2) NOT NULL,
[MultiServer] [int] NOT NULL,
CONSTRAINT [PK_tblMultiServerInfo] PRIMARY KEY CLUSTERED
(
[serverindex] ASC,
[m_idPlayer] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

And this is the CHARACTER.TBL SQL:

USE [CHARACTER_01_DBF]
GO
/****** Object: Table [dbo].[CHARACTER_TBL] Script Date: 01/07/2012 15:05:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CHARACTER_TBL](
[m_idPlayer] [char](7) NOT NULL,
[serverindex] [char](2) NOT NULL,
[account] [varchar](32) NOT NULL,
[m_szName] [varchar](32) NULL,
[playerslot] [int] NULL,
[dwWorldID] [int] NULL,
[m_dwIndex] [int] NULL,
[m_vScale_x] [real] NULL,
[m_dwMotion] [int] NULL,
[m_vPos_x] [real] NULL,
[m_vPos_y] [real] NULL,
[m_vPos_z] [real] NULL,
[m_fAngle] [real] NULL,
[m_szCharacterKey] [varchar](32) NULL,
[m_nHitPoint] [int] NULL,
[m_nManaPoint] [int] NULL,
[m_nFatiguePoint] [int] NULL,
[m_nFuel] [int] NULL CONSTRAINT [DF_CHARACTER_TBL_m_nFuel] DEFAULT ((-1)),
[m_dwSkinSet] [int] NULL,
[m_dwHairMesh] [int] NULL,
[m_dwHairColor] [int] NULL,
[m_dwHeadMesh] [int] NULL,
[m_dwSex] [int] NULL,
[m_dwRideItemIdx] [int] NULL,
[m_dwGold] [int] NULL,
[m_nJob] [int] NULL,
[m_pActMover] [varchar](50) NULL,
[m_nStr] [int] NULL,
[m_nSta] [int] NULL,
[m_nDex] [int] NULL,
[m_nInt] [int] NULL,
[m_nLevel] [int] NULL,
[m_nMaximumLevel] [int] NULL,
[m_nExp1] [bigint] NULL,
[m_nExp2] [bigint] NULL,
[m_aJobSkill] [varchar](500) NULL,
[m_aLicenseSkill] [varchar](500) NULL,
[m_aJobLv] [varchar](500) NULL,
[m_dwExpertLv] [int] NULL,
[m_idMarkingWorld] [int] NULL,
[m_vMarkingPos_x] [real] NULL,
[m_vMarkingPos_y] [real] NULL,
[m_vMarkingPos_z] [real] NULL,
[m_nRemainGP] [int] NULL,
[m_nRemainLP] [int] NULL,
[m_nFlightLv] [int] NULL,
[m_nFxp] [int] NULL,
[m_nTxp] [int] NULL,
[m_lpQuestCntArray] [varchar](3072) NULL,
[m_chAuthority] [char](1) NULL,
[m_dwMode] [int] NULL,
[m_idparty] [int] NULL,
[m_idCompany] [char](6) NULL,
[m_idMuerderer] [int] NULL,
[m_nFame] [int] NULL,
[m_nDeathExp] [bigint] NULL,
[m_nDeathLevel] [int] NULL,
[m_dwFlyTime] [int] NULL,
[m_nMessengerState] [int] NULL,
[blockby] [varchar](32) NULL,
[TotalPlayTime] [int] NULL,
[isblock] [char](1) NULL,
[End_Time] [char](12) NULL,
[BlockTime] [char](8) NULL,
[CreateTime] [datetime] NULL,
[m_tmAccFuel] [int] NULL,
[m_tGuildMember] [char](14) NULL,
[m_dwSkillPoint] [int] NULL,
[m_aCompleteQuest] [varchar](1024) NULL,
[m_dwReturnWorldID] [int] NULL,
[m_vReturnPos_x] [real] NULL,
[m_vReturnPos_y] [real] NULL,
[m_vReturnPos_z] [real] NULL,
[m_szNameCol] [varchar](32) NULL CONSTRAINT [DF__CHARACTER__m_szN__2739D489] DEFAULT ('' collate SQL_Latin1_General_Cp1_CI_AS),
[MultiServer] [int] NULL CONSTRAINT [DF_CHARACTER_MultiServer] DEFAULT ((0)),
[SkillPoint] [int] NOT NULL CONSTRAINT [DF_CHARACTER_SkillPoint] DEFAULT ((0)),
[SkillLv] [int] NOT NULL CONSTRAINT [DF_CHARACTER_SkillLv] DEFAULT ((0)),
[SkillExp] [bigint] NOT NULL CONSTRAINT [DF_CHARACTER_SkillExp] DEFAULT ((0)),
[dwEventFlag] [bigint] NULL CONSTRAINT [DF_CHARACTER_dwEventFlag] DEFAULT ((0)),
[dwEventTime] [int] NULL CONSTRAINT [DF_CHARACTER_dwEventTime] DEFAULT ((0)),
[dwEventElapsed] [int] NULL CONSTRAINT [DF_CHARACTER_dwEventElapsed] DEFAULT ((0)),
[AngelExp] [bigint] NULL CONSTRAINT [DF_CHARACTER_AngelExp] DEFAULT ((0)),
[AngelLevel] [int] NOT NULL CONSTRAINT [DF_CHARACTER_AngelLevel] DEFAULT ((0)),
[PKValue] [int] NOT NULL CONSTRAINT [DF_CHARACTER_PKValue] DEFAULT ((0)),
[PKPropensity] [int] NOT NULL CONSTRAINT [DF_CHARACTER_PKPropensity] DEFAULT ((0)),
[PKExp] [int] NOT NULL CONSTRAINT [DF_CHARACTER_PKExp] DEFAULT ((0)),
[m_dwPetId] [int] NULL CONSTRAINT [DF__CHARACTER__m_dwP__489AC854] DEFAULT ((-1)),
[m_nExpLog] [int] NULL CONSTRAINT [DF__CHARACTER__m_nEx__55F4C372] DEFAULT ((0)),
[m_nAngelExpLog] [int] NULL CONSTRAINT [DF__CHARACTER__m_nAn__56E8E7AB] DEFAULT ((0)),
[m_nCoupon] [int] NOT NULL CONSTRAINT [DF__CHARACTER__m_nCo__607251E5] DEFAULT ((0)),
[serverindex_old] [char](2) NULL,
[m_idPlayer_old] [char](7) NULL,
[m_nLayer] [int] NULL CONSTRAINT [DF_CHARACTER_TBL_m_nLayer] DEFAULT ((0)),
[m_nHonor] [int] NULL CONSTRAINT [DF_CHARACTER_TBL_m_nHonor] DEFAULT ((-1)),
[m_aCheckedQuest] [varchar](100) NULL CONSTRAINT [DF_CHARACTER_TBL_m_aCheckedQuest] DEFAULT ('$'),
[idCampus] [int] NULL CONSTRAINT [DF_CHARACTER_TBL_idCampus] DEFAULT ((0)),
[m_nCampusPoint] [int] NULL CONSTRAINT [DF_CHARACTER_TBL_m_nCampusPoint] DEFAULT ((0)),
[tKeepTime] [int] NULL,
CONSTRAINT [PK_CHARACTER_TBL] PRIMARY KEY NONCLUSTERED
(
[m_idPlayer] ASC,
[serverindex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'??? ??? ???? ??? ??? ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CHARACTER_TBL', @level2type=N'COLUMN',@level2name=N'm_aCheckedQuest'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'?? Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CHARACTER_TBL', @level2type=N'COLUMN',@level2name=N'idCampus'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'??? ?? ???' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CHARACTER_TBL', @level2type=N'COLUMN',@level2name=N'm_nCampusPoint'

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-07 : 14:46:46

That table seriously needs normalisation.

You have checked that there are no leading spaces on the player_id columns, seeing as they are char?

--
Gail Shaw
SQL Server MVP

sinan92
Starting Member

13 Posts

Posted - 2012-01-07 : 14:49:56

What do you mean with leading spaces?
And this is just the SQL that the server made when I clicked edit
I didnt write the SQL

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-07 : 15:09:03

Someone designed that table, and it is a poor design. (I wasn't talking about the SQL script, that's just the representation of a table, it's also not the data that I suggested you post)

By leading spaces, I mean exactly that. Spaces ahead of a value. eg " abc". It's not the same as "abc ", so if any of the m_playerid values have leading spaces they may not match as you expect.

--
Gail Shaw
SQL Server MVP

sinan92
Starting Member

13 Posts

Posted - 2012-01-07 : 15:26:12

Thanks Ill check on that
And yea I am pretty new to programming I started this school year at a development school so I dont know much about it
But I didnt make those databases
Edit:
I cant seem to find anything like that it looks fine they are the same but it just doesnt find it

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-07 : 17:56:19

quote:
Originally posted by GilaMonster

(and if you want, post the data here and we can do a sanity check)

--
Gail Shaw
SQL Server MVP

sinan92
Starting Member

13 Posts

Posted - 2012-01-07 : 18:07:24

What data should I post
I am using MS SQL DB Management Server

sinan92
Starting Member

13 Posts

Posted - 2012-01-07 : 20:19:29

Well from what I see they are the same..
But when I try to make a relation between them it says there is a collation

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-07 : 20:59:53

Do you know if there are any rows in tblMultiServerInfo has the same m_idPlayer value as the m_idPlayer value in any row in CHARACTER_TBL? All the indications from what you have posted is that there aren't any. You can test it as follows.

First run this query - it will return matching rows in CHARACTER_TBL:

SELECT * FROM CHARACTER_TBL c
WHERE EXISTS 
(
	SELECT * FROM tblMultiServerInfo m 
	WHERE c.m_idPlayer = m.m_idPlayer
);

If that returns no rows at all, run this query. Here, I am trying to eliminate the possibility that they are not matching up because of leading or trailing spaces.

SELECT * FROM CHARACTER_TBL c
WHERE EXISTS 
(
	SELECT * FROM tblMultiServerInfo m 
	WHERE 
		LTRIM(RTRIM(CAST(c.m_idPlayer AS VARCHAR(7)))) 
		= 
		LTRIM(RTRIM(CAST(m.m_idPlayer AS VARCHAR(7))))
);

If still you don't get any rows, then run this. Here, I am trying to eliminate the possibility that the collations are different. However, this is unlikely because if the collations were different, I think you would have gotten an error when running your original query. Nonetheless, here it is:

SELECT * FROM CHARACTER_TBL c
WHERE EXISTS 
(
	SELECT * FROM tblMultiServerInfo m 
	WHERE 
		LTRIM(RTRIM(CAST(c.m_idPlayer AS VARCHAR(7)))) COLLATE SQL_Latin1_General_CP1_CI_AS
		= 
		LTRIM(RTRIM(CAST(m.m_idPlayer AS VARCHAR(7)))) COLLATE SQL_Latin1_General_CP1_CI_AS
);

If that does not show any rows either, then post some sample data where you think there are matching rows.

Also, are you trying to run the query from a client code, or in SSMS query window. Try it in SSMS window to eliminate the possibility that the client code may be messing with you(r mind).

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources