help upgrading query (join to another 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 2005 Forums

Transact-SQL (2005)

help upgrading query (join to another table?)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-20 : 03:29:27

Hi,

I have a query that brings backs some stats on a user and works perfectly, I have pasted it below.

I have a table "tblUserDetails"

I would like to bring back all the records from tblUserDetails WHERE all the below COUNT(*)'s are equal to 100. This condition applies to all 6 columns that are brought back below.

I'm not exactly sure how to go about joining these, any help is much appreciated.

Thanks once again
mike123




CREATE PROCEDURE [dbo].[select_ProfileCompletePercentage]  
	
AS SET NOCOUNT ON 

SELECT count(*) as totalPhotos 

	,(SELECT count(*) FROM tblextraphotos WHERE userID = @userID and datalength(caption) > 2 and photoID <> 99) as totalPhotos_withCaptions 
	,(SELECT count(*) FROM tblinterests WHERE userID = @userID) as totalInterests
	,(SELECT count(*) FROM tblquestions WHERE userID = @userID and datalength(response) > 30) as totalQuestions_withFill
	,(SELECT count(*) FROM tblReferalLog where userID = @userID) as totalReferals
	,(SELECT count(*) FROM tblUserDetails where userID = @userID and datalength(userNote) > 5) as totalUserNote_withFill
 
FROM tblextraphotos WHERE photoID <> 99 and userID = @userID

Edit:removed @userID paramenter passed to SPROC

Kristen
Test

22859 Posts

Posted - 2007-09-20 : 19:29:58

[code]
SELECT *
FROM
(
SELECT count(*) as totalPhotos

,(SELECT count(*) FROM tblextraphotos WHERE userID = @userID and datalength(caption) > 2 and photoID <> 99) as totalPhotos_withCaptions
,(SELECT count(*) FROM tblinterests WHERE userID = @userID) as totalInterests
,(SELECT count(*) FROM tblquestions WHERE userID = @userID and datalength(response) > 30) as totalQuestions_withFill
,(SELECT count(*) FROM tblReferalLog where userID = @userID) as totalReferals
,(SELECT count(*) FROM tblUserDetails where userID = @userID and datalength(userNote) > 5) as totalUserNote_withFill

FROM tblextraphotos WHERE photoID <> 99 and userID = @userID
) AS X
WHERE totalPhotos = 100 AND totalPhotos_withCaptions = 100 ...
[/code]
Kristen

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-20 : 20:56:41

Hi Kristen,

I want to only bring back tblUserDetails.userID in my query, all userID's that have counts of 100 for all the columms. Sorry if that wasn't clear. I will not be passing a @userID paramenter to this query.

I think I will have to strip all the "WHERE userID = @userID" and do a JOIN somehow?

Thanks again!,
Mike123

Kristen
Test

22859 Posts

Posted - 2007-09-20 : 21:20:30

Might not be the most elegant way to code it, but seeing as it as easier to Cut&paste!!:


SELECT userID
FROM
(
SELECT userID
        , count(*) as totalPhotos 
	,(SELECT count(*) FROM tblextraphotos WHERE userID = @userID and datalength(caption) > 2 and photoID <> 99) as totalPhotos_withCaptions 
	,(SELECT count(*) FROM tblinterests WHERE userID = @userID) as totalInterests
	,(SELECT count(*) FROM tblquestions WHERE userID = @userID and datalength(response) > 30) as totalQuestions_withFill
	,(SELECT count(*) FROM tblReferalLog where userID = @userID) as totalReferals
	,(SELECT count(*) FROM tblUserDetails where userID = @userID and datalength(userNote) > 5) as totalUserNote_withFill
 
FROM tblextraphotos WHERE photoID <> 99 -- and userID = @userID
GROUP BY userID
) AS X
WHERE totalPhotos = 100 AND totalPhotos_withCaptions = 100 ...

Kristen

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-20 : 23:14:01

Hi Kristen,

I'm getting this error, and can't figure out exactly why.

Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'FROM'.

Here is the code I am running, I stripped out all the "WHERE userID = @userID"'s that were left in the query. I'm not sure if I'm taking the right approach here? Any help is much appreciated!

Thanks again,
mike123


SELECT userID
	FROM
	(
	SELECT userID, 
		count(*) as totalPhotos,
		(SELECT count(*) FROM tblextraphotos WHERE datalength(caption) > 2 and photoID <> 99) as totalPhotos_withCaptions,
		(SELECT count(*) FROM tblinterests   as totalInterests,
		(SELECT count(*) FROM tblquestions WHERE datalength(response) > 30) as totalQuestions_withFill,
		(SELECT count(*) FROM tblReferalLog as totalReferals,
		(SELECT count(*) FROM tblUserDetails where  datalength(userNote) > 5) as totalUserNote_withFill
		
	
		FROM tblextraphotos EP WHERE photoID <> 99 -- and userID = @userID
		GROUP BY userID
	) AS X


WHERE 

	totalPhotos => 17
	AND totalPhotos_withCaptions = 17
	AND totalInterests =>
	AND totalQuestions_withFill => 6
	AND totalReferals => 100
	AND totalUserNote_withFill => 1

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 03:23:14

There are several syntax errors:

AND totalInterests =>

which needs a value to compare against.

You need to use ">=" rather than "=>"

You are also missing a closing bracket:

(SELECT count(*) FROM tblinterests) as totalInterests,
(SELECT count(*) FROM tblReferalLog) as totalReferals,

Kristen

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-21 : 04:53:07

Hey Kristen,

Apologizes for my sloppiness

been a mind numbing day at the computer here!

I got the syntax fixed, but I'm not bringing the exact results I was looking to bring back. The count(*) columns are the count for the whole table for every row, not the users specific count.

For example this is what the data looks like, the 2nd and 3rd columns should be unique to the userID.

Thanks very much!
mike123

835121 18473 744576
205699 18473 744576
308110 18473 744576
132657 18473 744576


SELECT TOP 20 x.userID, x.totalPhotos_withCaptions,x.totalQuestions_withFill
	FROM
	(
	SELECT userID, 
		count(*) as totalPhotos,
		(SELECT count(*) FROM tblextraphotos WHERE datalength(caption) > 2 and photoID <> 99) as totalPhotos_withCaptions,
		(SELECT count(*) FROM tblinterests)   as totalInterests,
		(SELECT count(*) FROM tblquestions WHERE datalength(response) > 30) as totalQuestions_withFill,
		(SELECT count(*) FROM tblReferalLog) as totalReferals,
		(SELECT count(*) FROM tblUserDetails where  datalength(userNote) > 5) as totalUserNote_withFill 
	
		FROM tblextraphotos EP WHERE photoID <> 99  
		GROUP BY userID
	) AS X

JOIN tblUserDetails UD on x.userID = UD.userID

WHERE 

	totalPhotos >= 17
	AND totalPhotos_withCaptions >= 17
	AND totalInterests >= 5
	AND totalQuestions_withFill >= 6
	AND totalReferals >= 100
	AND totalUserNote_withFill >= 1
	 

ORDER BY lastLoggedIn DESC

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 05:43:05

OK, I should have spotted that.

You need to change each of the COUNT(*) sub-selects to something like:

(SELECT count(*) FROM tblextraphotos AS EP2 WHERE EP2.UserIDColumnName = EP.userID datalength(caption) > 2 and photoID <> 99) as totalPhotos_withCaptions,

Kristen

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-21 : 06:41:34

perfect, thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources