Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 30th, 2011, 08:14 AM
Authorized User
 
Join Date: Jul 2008
Posts: 24
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via MSN to ramone_johnny
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
__________________
Works Media - Online Marketing & SEO
 
Old July 13th, 2011, 04:10 AM
Registered User
 
Join Date: Oct 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT Tbl1.Field1, Tbl1.Field2, Tbl2.Field1, TblN.FieldN
FROM TableX Tbl1
INNER JOIN TableY Tbl2 ON Tbl1.mem_ID = Tbl2.profile_userID
INNER JOIN TableZ TblN On ....





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Statement Khalil Visual Basic 2008 Essentials 6 June 19th, 2010 10:22 PM
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 12:33 PM
SQL Statement SAMBUSA SQL Language 2 December 1st, 2004 11:10 AM
Help with SQL statement acdsky SQL Language 3 June 18th, 2004 04:53 AM
Sql Statement help morpheus Classic ASP Basics 0 March 9th, 2004 10:55 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.