Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    11

    Unanswered: Using results from one query in another

    First of all hello everyone, I'm new to the forum and have very little experience with databases.

    I have read only access to a MySQL database with 2 tables:

    "venue" with fields: venue_id, name, capacity, weekend_price, weekday_price, licensed

    "venue_booking" with fields: venue_id, date_booked

    I have a html page with a form with 2 inputs: date, and party size.
    Then i have a php page which takes these two values as input, and tries to output a table with: name, weekend_price, weekday_price for the venues that fit the criteria.

    I'm a bit lost as how to do this..
    I've got "SELECT venue_id FROM $venue_booking WHERE date_booked = '$date'", which gives me the venue id's for the venues which are NOT available.
    I then need to search for the names and prices of all the venues which do NOT have any of those id's in the other table, and output the fields.

    Any help would be greatly appreciated, and please bear in mind that my experience is limited.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT venue.venue_id
         , venue.name
         , venue.capacity
         , venue.weekend_price
         , venue.weekday_price
         , venue.licensed
      FROM venue
    LEFT OUTER
      JOIN venue_booking
        ON venue_booking.venue_id = venue.venue_id
       AND venue_booking.date_booked = '$date'
     WHERE venue_booking.venue_id IS NULL  
       AND venue.capacity >= $partysize
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2012
    Posts
    11
    Thanks a lot, works perfectly!

  4. #4
    Join Date
    May 2012
    Posts
    11
    Ok I've now got a third table which I need to access:

    catering: venue_id, grade, cost

    The fields I want this time are:
    venue.venue_id, venue.name, venue.weekend_price, venue.weekday_price, venue.licensed, catering.cost

    under the conditions:

    venue.capacity >= $partySize
    catering.grade = $grade

    and the previous condition so that only the venue_id's which are not booked on $date show up.

    Thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    presumably you need to find a matching catering row with desired grade, so this would be an INNER JOIN with the venue table, which you should add ahead of the LEFT OUTER JOIN

    give it a try and post it if it doesn't work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2012
    Posts
    11
    Wow I'm really bad at this.. Here's what I've got:

    SELECT
    venue.name,
    venue.weekend_price,
    venue.weekday_price,
    venue.licensed,
    catering.cost
    FROM
    venue,
    catering
    LEFT JOIN
    catering
    ON
    catering.venue_id = venue.venue_id
    AND
    catering.grade = '$grade'
    LEFT OUTER JOIN
    venue_booking
    ON
    venue_booking.venue_id = catering.venue_id
    AND
    venue_booking.date_booked = '$date'
    WHERE
    venue_booking.venue_id IS NULL
    AND
    venue.capacity >= $partySize


    But it doesn't work.. (probably because I'm not familiar with the syntax)

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --
    Code:
    FROM
    venue,
    catering
    LEFT JOIN
    catering
    ON
    catering.venue_id = venue.venue_id
    AND 
    catering.grade = '$grade'
    to this --
    Code:
      FROM venue
    INNER
      JOIN catering
        ON catering.venue_id = venue.venue_id
       AND catering.grade = '$grade'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2012
    Posts
    11
    Thanks a million!

Posting Permissions

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