query not getting desired results

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 2005 Forums

Transact-SQL (2005)

query not getting desired results

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-04-10 : 10:56:04

Hi,

I have a query that I am trying to run, but I discovered I am not getting the desired results.

This query here goes across the tables as seen below. One thing that I notice about this query as I am trying to figure out the source of the problem is that it is bringing back the same results even when I change the @userID parameter.

As long as the @userID I pass exists in "tblUserDetails" then we get results back. When it doesnt exist we dont get any records.

Basically to explain whats going on. I am passing the userID of the person who has "photocomments"

Since the table "tblPhotoComments" doesnt have a column that represents who the photocomment was for, we must join

tblExtraPhotos.counterID = tblPhotoComments.photoCounterID

This way we can get details of the photo, including the owner (tblextraphotos.userID)

Any help is much appreciated..

thanks again,
mike123

CREATE PROCEDURE [dbo].[select_Photocomments_ManageMy]
(
@userID int
)
AS SET NOCOUNT ON

SELECT TOP 500 photoCommentID, PC.active, comment, commentFromID,
UD1.nameOnline as commentFrom_NameOnline, PC.commentDate, PC.photoCounterID

FROM tblPhotoComments PC

JOIN tblUserDetails UD1 on PC.CommentFromID = UD1.userID
JOIN tblExtraPhotos EP on EP.counterID = PC.photoCounterID
JOIN tblUserDetails UD2 on EP.userID = UD1.userID

WHERE UD2.userID = @userID AND deletedByRecipient = 0

ORDER by PC.commentDate DESC

CREATE TABLE [dbo].[tblUserDetails](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL
)

GO

CREATE TABLE [dbo].[tblExtraPhotos](
[counterID] [int] IDENTITY(1,1) NOT NULL,
[photoID] [tinyint] NOT NULL,
[userID] [int] NOT NULL,
[photoDate] [smalldatetime] NOT NULL,
[caption] [varchar](50) NULL,
[status] [tinyint] NOT NULL

GO

CREATE TABLE [dbo].[tblPhotoComments](
[photoCommentID] [int] IDENTITY(1,1) NOT NULL,
[photoCounterID] [int] NOT NULL,
[CommentFromID] [int] NOT NULL,
[Comment] [varchar](1000) NOT NULL,
[commentDate] [smalldatetime] NOT NULL,
[Active] [tinyint] NOT NULL,
[deletedbySender] [tinyint] NOT NULL,
[deletedbyRecipient] [int] NOT NULL,
[IP] [varchar](15) NOT NULL
) ON [PRIMARY]

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 11:05:30

[code]SELECT TOP 500
{missing table alias}.photoCommentID,
PC.active,
{missing table alias}.comment,
{missing table alias}.commentFromID,
UD1.nameOnline as commentFrom_NameOnline,
PC.commentDate,
PC.photoCounterID
FROM tblPhotoComments AS PC
INNER JOIN tblUserDetails AS UD1 on PC.CommentFromID = UD1.userID
INNER JOIN tblExtraPhotos AS EP on EP.counterID = PC.photoCounterID
LEFT JOIN tblUserDetails AS UD2 on EP.userID = UD1.userID
AND UD2.userID = @userID
WHERE {missing table alias}.deletedByRecipient = 0
ORDER by PC.commentDate DESC[/code]By now, you surely must have learned how to prefix all columns with table alias?

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-04-10 : 11:55:34

quote:

Originally posted by Peso

SELECT		TOP 500
		{missing table alias}.photoCommentID,
		PC.active,
		{missing table alias}.comment,
		{missing table alias}.commentFromID, 
		UD1.nameOnline as commentFrom_NameOnline,
		PC.commentDate,
		PC.photoCounterID 
FROM		tblPhotoComments AS PC
INNER JOIN	tblUserDetails AS UD1 on PC.CommentFromID = UD1.userID 
INNER JOIN	tblExtraPhotos AS EP on EP.counterID = PC.photoCounterID 
LEFT JOIN	tblUserDetails AS UD2 on EP.userID = UD1.userID 
			AND UD2.userID = @userID
WHERE		{missing table alias}.deletedByRecipient = 0 
ORDER by	PC.commentDate DESC

By now, you surely must have learned how to prefix all columns with table alias?

E 12°55'05.25"
N 56°04'39.16"

Hi Peso,

Actually I usually only prefix the names when we have duplicate column names across tables

Should I prefix everything as best practice whenever I do a JOIN?

Also, I cant figure out what the problem is but when I run this query and pass the "@userID" value, I am still getting records that dont belong. For example passing @userID=500 should ensure that only "photocomments" for a "photoCounterID" that belong to user "500" are returned. This currently isn't the case altho everything you did looks right to me. Any idea ?

Thanks again for your patience and help!

mike123

SELECT TOP 500
PC.photoCommentID,
PC.active,
PC.comment,
PC.commentFromID,
UD1.nameOnline as commentFrom_NameOnline,
PC.commentDate,
PC.photoCounterID
FROM tblPhotoComments AS PC
INNER JOIN tblUserDetails AS UD1 on PC.CommentFromID = UD1.userID
INNER JOIN tblExtraPhotos AS EP on EP.counterID = PC.photoCounterID
LEFT JOIN tblUserDetails AS UD2 on EP.userID = UD1.userID
AND UD2.userID = @userID
WHERE PC.deletedByRecipient = 0
ORDER by PC.commentDate DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 14:47:52

Yes, you should always prefix column names with either table names or table alias.
Even though you posted relevant table DDL, it takes us just more time to figure these things out.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 14:53:46

[code]SELECT TOP 500
pc.photoCommentID,
pc.active,
pc.comment,
pc.commentFromID,
ud.nameOnline as commentFrom_NameOnline,
pc.commentDate,
pc.photoCounterID
FROM tblPhotoComments AS pc
INNER JOIN tblExtraPhotos AS ep ON ep.counterID = pc.photoCounterID
INNER JOIN tblUserDetails AS ud ON ud.userID = pc.CommentFromID
WHERE pc.deletedByRecipient = 0
ep.userID = @userID
ORDER BY pc.commentDate DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-04-10 : 15:31:11

quote:
Originally posted by Peso

Yes, you should always prefix column names with either table names or table alias.
Even though you posted relevant table DDL, it takes us just more time to figure these things out.

E 12°55'05.25"
N 56°04'39.16"

ahhhhhhhh ok sorry, for posting purposes .. my bad I won't make that mistake again :)

Its probably best to do it for my queries to for performance reasons ? I'm thinking it wouldn't hurt ?

Your new query appears to be working perfectly. (perfect so far with the data ive tested against)

this help me alot, once again thanks so much !!!

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources