Help with sql statement

Databases
1

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

2 
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'
     , MIN(Ph.tblph_filename) AS thePhoto 
     [COLOR="Blue"], mem.mem_gender
     , mem.mem_smoker
     , mem.mem_orientation
     , mem.mem_age
     , mem.mem_occupation
     , mem.mem_pet
     , mem.mem_ID[/COLOR]
  FROM tblprofiles AS P 
[COLOR="blue"]INNER
  JOIN tblmembers AS mem
    ON mem.mem_ID = P.profile_userID[/COLOR]
INNER 
  JOIN tblprofile_photos Ph 
    ON P.profile_ID = Ph.tblph_profileID 
 WHERE P.profile_status = 1 
   AND P.profile_movedate <= '" & SQLDate(available) & "' " 
   AND P.profile_rent <= " & SQLNumber(profile_rent) & " "  
   AND P.profile_state = '" & SQLString(state) & "' "  
   AND P.profile_city = '" & SQLString(city) & "' "  
GROUP 
    BY 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)
     [COLOR="Blue"], mem.mem_gender
     , mem.mem_smoker
     , mem.mem_orientation
     , mem.mem_age
     , mem.mem_occupation
     , mem.mem_pet
     , mem.mem_ID[/COLOR]
3

Thanks,

Mind if I try that and let you know how I went? :slight_smile:

John

4

Got it dude, cheers! :slight_smile: