help w query (join on random row with temp table)

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 w query (join on random row with temp table)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-10-11 : 02:43:45

Hi,

I have a simple query that is working fine but it needs to be upgraded for added functionality and it seems to get quite complicated. I've reposted this from an old thread because my thread got way too confusing and I never got it solved.

The original select statement code is working fine and brings back 1 row of many that match the criteria. The problem occurs because the procedure is now needed to run simutaneously for a few users and not bring back the same record. (at least not everytime like it is now)

My solution which might not be the best (I'm open to suggestions!) is to randomize the row that is first selected, which would be from the UserDetails table. I have done this for a similar SPROC and it worked fine, however it just brought back a single row. This SPROC is more complicated because it joins onto another table and bring back up to 10 rows. I'm having problems figuring out how to JOIN onto the second table.

If I can provide any further information please let me know I'll be happy to post it..

Anybody have any insight or suggestions on this ?

Thanks very very much, once again !
mike123

(ORIGINAL SPROC)

CREATE PROCEDURE dbo.select_unverified_thumbs_extra
(
@status tinyint
)
AS SET NOCOUNT ON

select UD.userID, EP.counterID, EP.photoID, UD.nameOnline, UD.emailAddress FROM tblExtraPhotos EP JOIN tblUserDetails UD on EP.userID = UD.userID WHERE EP.userID = (select top 1 userID from tblExtraPhotos WHERE status=@status AND photoID <> '99' AND photoID <> '0' AND photoID <> '100') AND status = @status AND photoID <> '99' AND photoID <> '0' AND photoID <> '100' ORDER BY counterID asc

GO

Here is what I have so far on my new SPROC that includes the randomization part.

(NEW SPROC - IN PROGRESS)

CREATE PROCEDURE dbo.select_unverified_thumbs_extra
(
@status tinyint
)
AS SET NOCOUNT ON

--lets randomize it, so 2 people can do it at once

-- Create a temporary table with the same structure of userID, counterID etc
-- the table we want to select a random record from
CREATE TABLE #TempRandomUser
(
idNum int identity(1,1),
userID int,
counterID int,
photoID int,
nameOnline varchar(15),
emailAddress varchar(50)
)
declare @nRandNum int
declare @nRecordCount int

--begin

-- Dump the contents of the table to seach into the
-- temp. table
--INSERT INTO #RandomUser
--set identity_insert #TempRandomUser on

INSERT INTO #TempRandomUser(userID, counterID, photoID, nameOnline, emailAddress) 'NOT SURE WHAT TO DO HERE'

-- Get the number of records in our temp table
Select @nRecordCount = count(userID) From #TempRandomUser
-- Select a random number between 1 and the number
-- of records in our table
Select @nRandNum = Round(((@nRecordCount) * Rand() + 1), 0)
-- Select the record from the temp table with the
-- ID equal to the random number selected...
Select userID, counterID, photoID, nameOnline, emailAddress From #TempRandomUser
Where idNum = @nRandNum

SET NOCOUNT OFF

GO

DDL & DML

CREATE TABLE [dbo].[tblUserDetails] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[NameOnline] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblExtraPhotos] (
[counterID] [int] IDENTITY (1, 1) NOT NULL ,
[photoID] [tinyint] NOT NULL ,
[userID] [int] NOT NULL ,
[status] [tinyint] NOT NULL
) ON [PRIMARY]
GO

Below I have created 3 rows in the userdetails. I have also created 3 sets of photos in the extraphoto table. ( I have inserted the identity columns from tbluserdetails userID 1,2,3)

We can see that the second user bill's info is not wanted since all of his photo statuses are '1'.

We want to randomly pick a user that has photo status values of 0 which leaves bob or jack. Once we get that userID established, we want to select all photos with status '0'

I hope this make things clear, if not please let me know

INSERT INTO tblUserDetails (nameOnline, emailaddress) VALUES ('bob','bob@hotmail.com')
INSERT INTO tblUserDetails (nameOnline, emailaddress) VALUES ('bill','bill@hotmail.com')
INSERT INTO tblUserDetails (nameOnline, emailaddress) VALUES ('jack','jack@hotmail.com')

INSERT INTO tblExtraPhotos (counterID, photoID, userID, status) VALUES ('1','1','1','0')
INSERT INTO tblExtraPhotos (counterID, photoID, userID, status) VALUES ('2','2','1','0')
INSERT INTO tblExtraPhotos (counterID, photoID, userID, status) VALUES ('3','3','1','0')

INSERT INTO tblExtraPhotos (counterID, photoID, userID, status) VALUES ('4','1','2','1')
INSERT INTO tblExtraPhotos (counterID, photoID, userID, status) VALUES ('5','2','2','1')
INSERT INTO tblExtraPhotos (counterID, photoID, userID, status) VALUES ('6','3','2','1')

INSERT INTO tblExtraPhotos (counterID, photoID, userID, status) VALUES ('7','1','3','0')
INSERT INTO tblExtraPhotos (counterID, photoID, userID, status) VALUES ('8','2','3','0')
INSERT INTO tblExtraPhotos (counterID, photoID, userID, status) VALUES ('9','3','3','0')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-11 : 10:30:51

I'm not sure I got completely understood the intention but I think this is it:

btw, you need "set identity_insert tblExtraPhotos on" for your DML


select	UD.userID
	,EP.counterID
	,EP.photoID
	,UD.nameOnline
	,UD.emailAddress 
from	dbo.[tblUserDetails] ud
join	dbo.[tblExtraPhotos] ep
		on ud.userid = (
			select top 1 userid 
			from tblExtraPhotos 
			where status = 0 
			order by newid())
where	ep.status = 0

Be One with the Optimizer
TG

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-10-12 : 01:04:30

Hi TG,

Not sure what happened but this query brought me back over 12,000 rows ( I had to stop the query) when there really was only 1 row. It seems it joined incorrectly or soemthing

Any suggestions? I am really unsure where to go with this.

Thanks again
mike123

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-12 : 04:00:02

Didnt you get solution from this?
http://sqlteam.com/forums/topic.asp?TOPIC_ID=56099

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-12 : 07:56:20

The one thing you're missing from your responses, mike123, from this thread as well as the one(s) referecened by madhivanan is an explicit posting of your desired output. If this doesn't do it, please provide details on what correct output should look like:


select	UD.userID
	,EP.counterID
	,EP.photoID
	,UD.nameOnline
	,UD.emailAddress 
from	dbo.[tblExtraPhotos] ep
join	(
	select	top 1
		UD.userID
		,UD.nameOnline
		,UD.emailAddress 
	from	dbo.[tblUserDetails] ud
	join	dbo.[tblExtraPhotos] ep
			on ud.userid = ep.userid
	where	ep.status = 0
	group by ud.userid, ud.nameOnline, ud.emailAddress --to equalize the ramdomness of userids
	order by newid()
	) ud on ud.userid = ep.userid

This produces output like either of these:


userID      counterID   photoID nameOnline      emailAddress                                       
----------- ----------- ------- --------------- ------------------
3           7           1       jack            jack@hotmail.com
3           8           2       jack            jack@hotmail.com
3           9           3       jack            jack@hotmail.com

userID      counterID   photoID nameOnline      emailAddress                                       
----------- ----------- ------- --------------- -------------------
1           1           1       bob             bob@hotmail.com
1           2           2       bob             bob@hotmail.com
1           3           3       bob             bob@hotmail.com

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources