SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist ramone_johnny's Avatar
    Join Date
    Jan 2004
    Location
    Brisbane - Australia
    Posts
    434
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Help with sql statement

    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
    					
    			' *************************************************************************************************************************
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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 
         , mem.mem_gender
         , mem.mem_smoker
         , mem.mem_orientation
         , mem.mem_age
         , mem.mem_occupation
         , mem.mem_pet
         , mem.mem_ID
      FROM tblprofiles AS P 
    INNER
      JOIN tblmembers AS mem
        ON mem.mem_ID = P.profile_userID
    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)
         , mem.mem_gender
         , mem.mem_smoker
         , mem.mem_orientation
         , mem.mem_age
         , mem.mem_occupation
         , mem.mem_pet
         , mem.mem_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist ramone_johnny's Avatar
    Join Date
    Jan 2004
    Location
    Brisbane - Australia
    Posts
    434
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks,

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

    John

  4. #4
    SitePoint Evangelist ramone_johnny's Avatar
    Join Date
    Jan 2004
    Location
    Brisbane - Australia
    Posts
    434
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Got it dude, cheers!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •