Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help with sql statement
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ramone_johnny
Starting Member

Australia
35 Posts

Posted - 06/30/2011 :  08:30:39  Show Profile  Reply with Quote
I have the following sql statement that works fine...

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


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
Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 06/30/2011 :  08:52:56  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

ramone_johnny
Starting Member

Australia
35 Posts

Posted - 06/30/2011 :  09:04:53  Show Profile  Reply with Quote
DUDE!

Works perfectly. THANK YOU!!

Complete newbie. Please forgive me for having to ask such stupid questions...
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000