help sorting out query (getting duplicate records)

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

SQL Server 2000 Forums

Transact-SQL (2000)

help sorting out query (getting duplicate records)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-07-21 : 19:05:29

I have the following query, which brings back the last 20 members online. The problem is that its bringing back effective duplicate records. I'll explain what I mean by that a bit further.

When a user logs on to the website, their SessionID and their userID (identity row in DB) is inserted into the online table. A user can be logged in twice under different sessionID's .. This is what is creating the problems. If this happens I only want one of the rows returned.

Any suggestions on how to modify this query?

Thanks once again!
mike123

create PROCEDURE dbo.select_online_members_top20

AS SET NOCOUNT ON

SELECT TOP 20

uq_users.userID, UD.nameOnline, UD.age, UD.genderID, UD.statusID,
UD.date, UD.userNote, UD.city, UD.stateProvID

FROM (SELECT userID, min(sessionID) AS sess FROM tblactive_users WHERE userID > '0' GROUP BY userID) AS uq_users

INNER JOIN tblactive_users AS info ON uq_users.sess = info.sessionID AND uq_users.userID = info.userID
JOIN tblUserDetails UD on UD.userID = uq_users.userID

WHERE ud.active = 1

ORDER BY sessionID DESC

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-21 : 19:11:19

Here's the link for how to provide the DDL and DML that we need:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-07-21 : 19:35:43

Hi Tara,

Sure, here it is :) Hope its ok, did the insert stuff by hand.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblActive_Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblActive_Users]
GO

CREATE TABLE [dbo].[tblActive_Users] (
[sessionID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userID] [int] NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__tblHotLis__UserI__38EE7070]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblHotList] DROP CONSTRAINT FK__tblHotLis__UserI__38EE7070
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblInstantMessage_tblUserDetails]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblInstantMessage] DROP CONSTRAINT FK_tblInstantMessage_tblUserDetails
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblMessage_tblUserDetails]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblMessage] DROP CONSTRAINT FK_tblMessage_tblUserDetails
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblModPoints_tblUserDetails]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblModPoints] DROP CONSTRAINT FK_tblModPoints_tblUserDetails
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblMOM_tblUserDetails]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblMOM] DROP CONSTRAINT FK_tblMOM_tblUserDetails
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblThumbs_tblUserDetails]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblThumbs] DROP CONSTRAINT FK_tblThumbs_tblUserDetails
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblUserDetails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUserDetails]
GO

CREATE TABLE [dbo].[tblUserDetails] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[NameOnline] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateProvID] [tinyint] NULL ,
[GenderID] [tinyint] NULL ,
[Age] [tinyint] NULL ,
[SexualityID] [tinyint] NULL ,
[StatusID] [tinyint] NULL ,
[Date] [datetime] NULL ,
[Active] [tinyint] NULL ,
[UserNote] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

INSERT INTO tblActive_users (userID, sessionID) VALUES ('1','ghixvv45sjif1w44vhz4nhvo')

INSERT INTO tblActive_users (userID, sessionID) VALUES ('1','ghixvv45sjif1w33vhz4nhvo')

INSERT INTO tblActive_users (userID, sessionID) VALUES ('2','ghixvv45sjif1w55vhz4nhvo')

INSERT INTO tblActive_users (userID, sessionID) VALUES ('3','ghixvv45sjif1w66vhz4nhvo')

INSERT INTO tblActive_users (userID, sessionID) VALUES ('4','ghixvv45sjif1w77vhz4nhvo')

INSERT INTO tblUserDetails (NameOnline,City,StateProvID,GenderID,Age,SexualityID,StatusID,Date,Active,UserNote) VALUES ('bob','bobcity',1,1,10,1,getDate(),1,'hello')

INSERT INTO tblUserDetails (NameOnline,City,StateProvID,GenderID,Age,SexualityID,StatusID,Date,Active,UserNote) VALUES ('bill','billcity',1,1,10,1,getDate(),1,'hello im bill')

INSERT INTO tblUserDetails (NameOnline,City,StateProvID,GenderID,Age,SexualityID,StatusID,Date,Active,UserNote) VALUES ('jack','jackcity',1,1,10,1,getDate(),1,'hello im jack')

INSERT INTO tblUserDetails (NameOnline,City,StateProvID,GenderID,Age,SexualityID,StatusID,Date,Active,UserNote) VALUES ('wanger','wangercity',1,1,10,1,getDate(),1,'hello im wanger')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-21 : 19:43:14

I had to tweak the inserts for tblUserDetails as StatusID was left out from the VALUES section. Also, always post with fake table names, temp tables, or table variables, so that in case you copy and paste and then run what we give back to you, you don't wipe out your real tables. Here you go:




CREATE TABLE [dbo].[#tblActive_Users] (
[sessionID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userID] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[#tblUserDetails] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[NameOnline] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateProvID] [tinyint] NULL ,
[GenderID] [tinyint] NULL ,
[Age] [tinyint] NULL ,
[SexualityID] [tinyint] NULL ,
[StatusID] [tinyint] NULL ,
[Date] [datetime] NULL ,
[Active] [tinyint] NULL ,
[UserNote] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

INSERT INTO #tblActive_users (userID, sessionID) VALUES ('1','ghixvv45sjif1w44vhz4nhvo')
INSERT INTO #tblActive_users (userID, sessionID) VALUES ('1','ghixvv45sjif1w33vhz4nhvo')
INSERT INTO #tblActive_users (userID, sessionID) VALUES ('2','ghixvv45sjif1w55vhz4nhvo')
INSERT INTO #tblActive_users (userID, sessionID) VALUES ('3','ghixvv45sjif1w66vhz4nhvo')
INSERT INTO #tblActive_users (userID, sessionID) VALUES ('4','ghixvv45sjif1w77vhz4nhvo')

INSERT INTO #tblUserDetails (NameOnline,City,StateProvID,GenderID,Age,SexualityID,StatusID,Date,Active,UserNote) VALUES ('bob','bobcity',1,1,10,1,1,getDate(),1,'hello')
INSERT INTO #tblUserDetails (NameOnline,City,StateProvID,GenderID,Age,SexualityID,StatusID,Date,Active,UserNote) VALUES ('bill','billcity',1,1,10,1,1,getDate(),1,'hello im bill')
INSERT INTO #tblUserDetails (NameOnline,City,StateProvID,GenderID,Age,SexualityID,StatusID,Date,Active,UserNote) VALUES ('jack','jackcity',1,1,10,1,1,getDate(),1,'hello im jack')
INSERT INTO #tblUserDetails (NameOnline,City,StateProvID,GenderID,Age,SexualityID,StatusID,Date,Active,UserNote) VALUES ('wanger','wangercity',1,1,10,1,1,getDate(),1,'hello im wanger')

SELECT
	uq_users.userID, 
	UD.nameOnline, 
	UD.age, 
	UD.genderID, 
	UD.statusID, 
	UD.date, 
	UD.userNote, 
	UD.city, 
	UD.stateProvID
FROM
(
	SELECT userID, MAX(sessionID) AS sessionID
	FROM #tblActive_users
	GROUP BY userID
) uq_users
INNER JOIN #tblUserDetails UD
ON uq_users.userID = UD.UserID

DROP TABLE #tblActive_users, #tblUserDetails

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-21 : 19:45:37

Hmmm, let me rework that. I think I see a problem.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-21 : 19:48:18

I think I have it right. I think that you just need to remove this from your sproc:

INNER JOIN tblactive_users AS info ON uq_users.sess = info.sessionID AND uq_users.userID = info.userID

We don't need to join to that table as we've already got the information we need from the derived table.

If my solution doesn't work for you, please provide the expected result set using the sample data that you provided. If the sample data doesn't reflect your problem, please provide better sample data and the expected result set.

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-07-21 : 20:11:23

Hi Tara,

I think I got it :).. I'll let you know if anything changes.

(I had an error on order by sessionID as I think we removed that join, however I was able to order it by userID which is good enough )

thanks once again
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources