[thelist] How do I properly query multiple tables?

Mark Marlow mark at testwiz.com
Wed Feb 9 16:41:55 CST 2005


Assuming a whole lot:

Try something like:

SELECT ed.*,e.*,c.* FROM tbl_eventDates ed
  JOIN tbl_event e ON e.eventId = ed.eventId
  JOIN tbl_link  l ON l.eventId = e.eventId
  JOIN tbl_contact c ON c.contactId = l.contactId
WHERE ed.eventDate = '1/1/2004'
ORDER BY ed.eventData
 

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Pringle, Ron
Sent: Wednesday, February 09, 2005 4:07 PM
To: theList (E-mail)
Subject: [thelist] How do I properly query multiple tables?

Having problems with my SQL Query for a Calendar of Events details page.
Using ASP and VBScript and connecting to an Access database.

I have a DB with multiple tables and am trying to query them and return
results based on a number of factors.

I first want to check the eventDates table and compare the eventDate field
to a querystring that is passed to the ASP page.

Then I want to grab all event records that match that date and link the
records together from all tables based on a primarykey field of eventID.

Some of the tables will have no values stored in them, as those fields are
optional.

I've looked at UNIONS and JOINS but neither seem to be working for me. Below
is the code I currently have, which of course isn't working quite properly:

SELECT * FROM tbl_eventDates, tbl_event, tbl_contact, tbl_link WHERE
eventDate = CDate(Request.QueryString("EventDate")) AND tbl_event.eventID =
tbl_eventDates.eventID ORDER BY tbl_eventDates.eventDate

The above query grabs all the info and does properly compare it to the
querystring and only shows the records that match that, but info from the
tbl_contact and tbl_link tables are out of order and not displaying with the
right records from the tbl_event table.

Can someone suggest how I should write my query and an explanation to go
along with it?

TIA

Regards,
Ron

-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester and archives
of thelist go to: http://lists.evolt.org Workers of the Web, evolt ! 



More information about the thelist mailing list