Help with sql statement - SQL Server Forums

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

SQL Server Development (2000)

Help with sql statement

Author

Topic

ramone_johnny
Starting Member

35 Posts

Posted - 2011-06-30 : 08:30:39

I have the following sql statement that works fine...

[CODE]strSQL = "SET DATEFORMAT DMY; SELECT P.profile_ID, P.profile_movedate, P.profile_views, P.profile_uniqueKEY, P.profile_datecreated, P.profile_headline, P.profile_rent, P.profile_banner, P.profile_city, P.profile_state, CONVERT(varchar(255), P.profile_description) AS 'desc'"

'DO WE SHOW ADS WITH PHOTOS OR NOT?

if request("ad_types") = "2" then
strSQL = strSQL & ", MIN(Ph.tblph_filename) AS thePhoto FROM tblprofiles AS P INNER JOIN tblprofile_photos Ph ON P.profile_ID = Ph.tblph_profileID WHERE P.profile_status = 1 "
else
strSQL = strSQL & " FROM tblprofiles AS P WHERE P.profile_status = 1 "
end if

ad_types = Request("ad_types")

' *******************************************************************************************************************

' ********************************************* 'SET THE DATE SELECTION PARAMETERS ****************************************

if request("when") <> "" then
when = CInt(Request("when"))
end if

Select Case when
Case 1:
available = Date() - Weekday(Date()) + 1
strSQL = strSQL & " AND P.profile_movedate <= '" & SQLDate(available) & "' "

Case 2:
available = Date() - Weekday(Date()) + 8
strSQL = strSQL & " AND P.profile_movedate > '" & SQLDate(available) & "' "

Case 3:
available = Date() - Weekday(Date()) + 16
strSQL = strSQL & " AND P.profile_movedate > '" & SQLDate(available) & "' "

Case 4:
available = Date() - Weekday(Date()) + 31
strSQL = strSQL & " AND P.profile_movedate > '" & SQLDate(available) & "' "

Case ELSE
'do nothing here as we are displaying ALL share listings
End Select

if profile_rent <> "" then
strSQL = strSQL & " AND P.profile_rent <= " & SQLNumber(profile_rent) & " "
end if

if state <> "" then
strSQL = strSQL & " AND P.profile_state = '" & SQLString(state) & "' "
end if

if city <> "" then
strSQL = strSQL & " AND P.profile_city = '" & SQLString(city) & "' "
end if

' *************************************************************************************************************************

strSQL = strSQL & "GROUP BY P.profile_ID, P.profile_movedate, P.profile_views, P.profile_uniqueKEY, P.profile_datecreated, P.profile_headline, P.profile_banner, P.profile_rent, P.profile_city, P.profile_state, CONVERT(varchar(255), P.profile_description) "

' *********************************** orderby THE RESULTS AS REQUESTED VIA DROPDOWN MENU ************************************

if orderby <> "" then
strSQL = strSQL & " ORDER BY " & orderby
else
strSQL = strSQL & " ORDER BY P.profile_datecreated DESC"
end if

' *************************************************************************************************************************[/CODE]

But I also need to gather information from a different table, and Im not sure how to do this? Sorry, Im not real great at SQL.

I need from the 'tblmembers' table.....

mem_gender
mem_smoker
mem_orientation
mem_age
mem_occupation
mem_pet
mem_ID

The relationship is between

mem_ID = profile_userID

Could someone help me out with this please?

Cheers,
John

Complete newbie. Please forgive me for having to ask such stupid questions...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 08:52:56

change the from cluause to
strSQL = strSQL & " FROM tblprofiles AS P join tblmembers m on m.mem_ID = p.profile_userID WHERE P.profile_status = 1 "
do a similar thing for the other from clause in the if statement.
strSQL = strSQL & ", MIN(Ph.tblph_filename) AS thePhoto FROM tblprofiles AS P INNER JOIN tblprofile_photos Ph ON P.profile_ID = Ph.tblph_profileID join tblmembers m on m.mem_ID = p.profile_userID WHERE P.profile_status = 1 "

then you can add the columns you need to the select part

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ramone_johnny
Starting Member

35 Posts

Posted - 2011-06-30 : 09:04:53

DUDE!

Works perfectly. THANK YOU!!

Complete newbie. Please forgive me for having to ask such stupid questions...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources