Need help with query to retrieve multiple table data 1

[Evil8]

I have the following table structure in mySQL database - meetingdb

This is the table stucture:

tbl_event
eventid - primary key
eventdate
eventtime
eventplaceID - foriegn key for tbl_venue.venueid
eventplanID - foriegn key for tbl_companyplan.planid

tbl_venue
venueid - primary key
name
address
city
state
zip

tbl_companyplan
planid - primary key
company
plantype

I need to list the data output like this:

name
date - time
address
city, state zip
company plantype

I'd like to pull dates after "today" if I can and sort the data by City then Date then Time on the html page

SELECT tbl_event.eventdate,
                  tbl_event.eventtime, 
                  tbl_venue.name, 
                  tbl_venue.address, 
                  tbl_venue.city, 
                  tbl_venue.state, 
                  tbl_venue.zip, 
                  tbl_companyplans.company, 
                  tbl_companyplans.plantype

FROM tbl_event (Join both tbl_venue and tbl_companyplan here???)

WHERE tbl_event.eventplaceID = tbl_venue.venueid || tbl_event.eventplanID = tbl_planid

ORDER BY 'city','state','eventdate','eventtime'

First I think all I need is help with the FROM and WHERE syntax and then how I can select those dates (another WHERE?) greater than "today" I think I may have it.

Thank you for any help! I do this so little of this type of work... 8)

 

[r937]

i would suggest that in future you refrain from sticking "tbl_" on the front of your table names, unless of course you also plan to stick "col_" on the front of your column names

everybody i suggest this to replies with "but that'd be silly" and i always answer "my point exactly"

SELECT tbl_event.eventdate
     , tbl_event.eventtime
     , tbl_venue.name
     , tbl_venue.address
     , tbl_venue.city
     , tbl_venue.state
     , tbl_venue.zip
     , tbl_companyplans.company
     , tbl_companyplans.plantype
  FROM tbl_event
INNER
  JOIN tbl_venue
    ON tbl_venue.venueid = tbl_event.eventplaceID
INNER
  JOIN tbl_companyplan
    ON tbl_companyplan.planid = tbl_event.eventplanID
ORDER
    BY tbl_venue.state
     , tbl_venue.city
     , tbl_event.eventdate
     , tbl_event.eventtime

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Evil8]

Thanks for the help! As far as putting tbl_ on the table names I'm used to Access and VB coding. We were trained to put tbl_ on tables and qyr_ on queries etc. Habits...

 

[r937]

Habits...

time to break this one, then



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Evil8]

I have changed all the table names and modified the sql query accordingly.