Help with query (temp table join ? )

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 with query (temp table join ? )

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-10-05 : 16:06:20

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.

The original select statement code is just this.

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.

A desired result would look something like this

1- 10 rows of these values (depending on how many exist)

userID, counterID, photoID, nameOnline, emailAddress
1, 101, 1, bob, bob@hotmail.com
1, 102, 2, bob, bob@hotmail.com
1, 103, 3, bob, bob@hotmail.com
1, 104, 4, bob, bob@hotmail.com
1, 105, 5, bob, bob@hotmail.com

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 once again !
mike123

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.

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

ReneL
Starting Member

3 Posts

Posted - 2005-10-05 : 22:27:33

try to use this technique to return a random record:

To return 5 random record:
select top 5 * from dbo.tablename order by newid()

To return 10 random record:
select top 10 * from dbo.tablename order by newid()

I think use you can use this approach. You don't need temporary table.
You can directly issue a select statement and you can use the join statement as well.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-10-06 : 00:21:21

Hi Renel,

I not having any luck with your suggestion. I think its more complicated than this and your idea won't work, but correct me if I am wrong.

Thanks!
mike

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-06 : 01:34:11

Post some more sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-10-06 : 04:02:23

Hi madhivanan,

I'll do my best with the sample data here. The first table is the "userdeails" table with the users. We are not selecting from this table but will join onto it.

The second table is where we select a random single row with

"select top 1 userID from tblExtraPhotos WHERE status=@status AND photoID <> '99' AND photoID <> '0' AND photoID <> '100'"

However I'd like to select this row randomly so I believe I must dump the top 500 or so into a temp table, and then select one from there. Once we have established this user has at least one row in the "extraphotos" table matching the criteria then we can bring back all the matching rows.

Thanks very much for the help! Please let me know if I can explain anything better and if this makes any sense :)

Mike123

tblUserDetails

TBLUSERDETAILS

userID,nameOnline,email
1, bob, bob@hotmail.com
2, bill, bill@hotmail.com
3, jack, jack@hotmail.com

TBLEXTRAPHOTOS

userID, counterID, photoID, status
1, 101, 1, 0
1, 102, 2, 0
1, 103, 3, 0
1, 104, 4, 0
1, 105, 5, 0
2, 106, 1, 1
2, 107, 2, 1
3, 108, 1, 0
3, 109, 2, 0
3, 110, 3, 0

Desired Result (one of 2 possible due to randomess factor)

userID, counterID, photoID, nameOnline, emailAddress, status
1, 101, 1, bob, bob@hotmail.com, 0
1, 102, 2, bob, bob@hotmail.com, 0
1, 103, 3, bob, bob@hotmail.com, 0
1, 104, 4, bob, bob@hotmail.com, 0
1, 105, 5, bob, bob@hotmail.com, 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-06 : 04:15:28

Try this

Select T2.userid,T1.nameonline, T1.email, T2.counterID, T2.photoid, T2.status from TBLUSERDETAILS T1 inner join TBLEXTRAPHOTOS T2 on
T2.userid=T1.userid

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-10-06 : 04:20:20

that statement does not bring back what I need. I had to stop the query after it brought back over 10k rows of the user table...

Plus I am not sure how u wanted it integrated into the temp table design?

Cheers,
Mike

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-06 : 04:50:17

See if this is what you wanted

Select T2.userid,T1.nameonline, T1.email, T2.counterID, T2.photoid, T2.status from
(Select top 1 * from TBLUSERDETAILS order by newid()) T1
inner join TBLEXTRAPHOTOS T2 on T2.userid=T1.userid

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-10-06 : 15:07:55

sort of, it doesnt take into account that it can't just be any random record in the tbluserdetails.. it has to be a record with corresponding rows in the extraphotos table with status = 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-06 : 15:13:30

Mike, please post the DML and DDL so that we can help you.

Here's an example:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29085

And here's why:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-10-06 : 18:33:35

Tara to the rescue! :) I was hoping for maybe a quick reply but ya now that I know your looking at it I definately did up the DML and DDL for you.

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

thanks very much, once again!
mike123

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

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')

ReneL
Starting Member

3 Posts

Posted - 2005-10-06 : 23:20:26

Try this solution

select a.userid,
b.counterid,
b.photoid,
a.NameOnline,
a.EmailAddress,
b.status
from tblUserDetails a
inner join (
select *
from tblExtraPhotos p1
where p1.status = 0
and p1.userid in (
select top 1 p2.userid
from tblExtraPhotos p2
where status = 0
order by newid())) as b on a.userid = b.userid

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-10-07 : 01:02:06

Hi ReneL,

Thanks but I only ever get back 1 row with that query, not exactly what I'm lookin for

mike123

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-10-13 : 01:37:07

Hello mike123,

Based on your explanation and data I gave it a try. Had to clean up your DDL and DML a bit to make it execute, so included my modifications as well. You should be able to re-run the query a few times to see that it works.

Not sure if a subquery is the most efficient method, but it was the first thing that came to mind for getting a random userid...

-- create tables and insert sample data
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

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 (photoid, userid, status) VALUES ('1','1','0')
INSERT INTO tblExtraPhotos (photoid, userid, status) VALUES ('2','1','0')
INSERT INTO tblExtraPhotos (photoid, userid, status) VALUES ('3','1','0')

INSERT INTO tblExtraPhotos (photoid, userid, status) VALUES ('1','2','1')
INSERT INTO tblExtraPhotos (photoid, userid, status) VALUES ('2','2','1')
INSERT INTO tblExtraPhotos (photoid, userid, status) VALUES ('3','2','1')

INSERT INTO tblExtraPhotos (photoid, userid, status) VALUES ('1','3','0')
INSERT INTO tblExtraPhotos (photoid, userid, status) VALUES ('2','3','0')
INSERT INTO tblExtraPhotos (photoid, userid, status) VALUES ('3','3','0')


-- select top 10 photos for a random user having photos with status 0
SELECT		TOP 10
		u.userid
	,	p.counterid
	,	p.photoid
	,	u.nameonline
	,	u.emailaddress

FROM
		tblUserDetails	u
	,	tblExtraPhotos	p
WHERE
		u.userid	=	p.userid
AND
		p.status	=	0
AND
		u.userid	=	(SELECT		TOP 1
							userid
					FROM
							tblExtraPhotos
					WHERE
							status	=	0
					ORDER BY
							NewID()	)


-- clean up
DROP TABLE tblUserDetails
DROP TABLE tblExtraPhotos

If you want to randomize the results (photos) thar are returned, simply use ORDER BY NewID() on the main query as well.

Hope that helps!

Daniel

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-10-17 : 16:47:50

Hi Corp Direct,

Thanks very much, I believe this is perfect!

For future reference is this order by NewID() a scalable solution ? Does it perform better than a temp table? Is it even possible to do this with a temp table?

Thanks again
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources