Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    19

    Unanswered: How do I write a query that joins/searches 90 small tables?

    I'm creating an electronic calendar customers can use to schedule appointments by clicking a button with their mouse.

    I wrote php code that uses a for loop to create the days of one calendar month. I wrote a separate for loop that creates one mysql table per day of that same month. Each table represents one day of the month, and holds scheduled appointments for that day.

    A registration table holds data customers send to it by submitting the registration form. The customer user id is the primary key for the registration table.

    The appointment time is the primary key for the daily appointment tables. The user id is the foreign key for these tables.

    I want to run one query that searches these tables for appointments that have been scheduled (inserted) into these tables. This query will be run on customer login.

    Searching just one month worth of daily tables requires a query that can join 31 tables for a search of January appointments (for example), or even 90+ tables for a search of Jan, Feb and March tables.

    I've been reading up on queries, and most of what I read explains how to join two or three tables. How do I write a query that joins 31 or 9o+ tables?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by quartz View Post
    How do I write a query that joins 31 or 9o+ tables?
    you don't

    you redesign your data so that all appointments are in a single appointments table

    one table per date is unrealistic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2012
    Posts
    19
    Thank you. You saved me a lot of mistakes.

Posting Permissions

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