SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Query to return associated data and not what is searched for

    Hi guys!

    I've written a query that is the equivalent of asking someone to go find a sock but to also return anything else they found along with it.

    I have bookings, each of which have a one-to-many relationship with one to [n] data objects. While I'm asking the database to find one of those data objects, I actually need it to retrieve any other data objects it might find. Alas, it's not working that way and it's only returning what it's finding.

    I've emboldened the part of the query which is the source of my angst.

    I'm sure this is a very common problem, but I can't articulate the problem to even search for a solution!

    As always, any advice would be warmly received.

    Code:
    SELECT
        bookings.booking_id,
        DATE_FORMAT(bookings.creation_date, '%a %D %b %y') AS creation_date,
        DATE_FORMAT(bookings.modification_date, '%a %D %b %y') AS modification_date,
        bookings.client_id,
        bookings_attendees.name,
        DATE_FORMAT(bookings_attendees.stay_date, '%a %D %b %y') AS stay_date,
        bookings_attendees.stay_nights,
        venues.venue_id AS venue_id,
        venues.name AS venue,
        venues.town_city,
        DATE_FORMAT((
            SELECT
                MAX(bookings_history.modification_date) AS booking_history
            FROM bookings_history
            WHERE
                (bookings_history.booking_id = bookings.booking_id)
        ), '%a %D %b %y, %H:%i') AS booking_history,
        COALESCE(GROUP_CONCAT(DISTINCT bookings_clients_options_data.value SEPARATOR 0x1D), 'NULL') AS clients_options
    FROM
        bookings_attendees
    INNER JOIN
        bookings
    ON
        (bookings.booking_id = bookings_attendees.booking_id)
    AND
        (bookings.client_id = '3')
    AND
        (bookings.status = 'confirmed')
    AND
        (bookings.mode = 'public')
    INNER JOIN 
        bookings_clients
    ON
        (bookings.booking_id = bookings_clients.booking_id)
    INNER JOIN
        clients
    ON
        (clients.client_id = bookings_clients.client_id)
    AND
        (clients.client_id = '1')
    INNER JOIN
        venues
    ON
        (venues.venue_id = bookings.venue_id)
    INNER JOIN
        bookings_clients_options
    ON
        (bookings_clients_options.client_id = '3')
    INNER JOIN
        bookings_clients_options_data
    ON
        (bookings_clients_options.bookings_client_option_id = bookings_clients_options_data.bookings_client_option_id)
    AND
        (bookings_clients_options_data.booking_attendee_id = bookings_attendees.booking_attendee_id)
    WHERE
        (bookings_clients_options_data.bookings_client_option_id = '4')
    GROUP BY bookings.booking_id
    ORDER BY bookings_attendees.stay_date
    ASC

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Forbes View Post
    I have bookings, each of which have a one-to-many relationship with one to [n] data objects. While I'm asking the database to find one of those data objects, I actually need it to retrieve any other data objects it might find.
    this is a bit nebulous... please rephrase it in terms of all seven tables in your query

    Quote Originally Posted by Forbes View Post
    Alas, it's not working that way and it's only returning what it's finding.
    think about this for a minute and then tell me you wouldn't want sql to work any other way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay:
    1. bookings and bookings_attendees both contain the actual booking data (split because it supports more than one booking per person made in one session);
    2. clients isn't necessarily required here, but is necessary for when other types of user are using the system, and is required to determine which client they belong to;
    3. venues is the venue to which the booking relates;
    4. bookings_clients_options contains the custom fields which the client can use to capture data beyond the mandatory fields required for the booking (name, email, venue, stay date, number of nights et cetera);
    5. bookings_clients_options_data is for the actual data objects themselves.

    As for your question .. think about my opening sentence for a minute and then tell me if you realised I was being sarcastic.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    if you were being sarcastic then i am sorry because i don't see from your query why if it's not finding what it wasn't asked to find that this is a problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, it is finding the "sock", but it's not returning the "sofa" it was behind and the "carpet" they both sat on. It's an example I used to the client to explain how weird the query needs to be.

    Okay, so booking 54,321 has three data objects; 123, 124, and 125. I run the query to see if data object 125 is the sock, and if so, return 123 "sofa" and 124 "carpet", too. Or, more specifically, any data objects it can find. Presently, it's only returning what it finds, which is the sock.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    why does one of your joins specify client_id 1 and another client_id 3?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because there's a parent-child relationship to the clients. In this instance, the company the system belongs to is the client and their customer "client" is a child of that.

    I wouldn't be concerned with the client side of things; it's a piece of code that's present in about 70% of the queries throughout the system, and is working.

    I'll confess, it's not the ideal structure (I intend changing this at a later date), but it was a design forced on me by circumstance at the time.

  8. #8
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Over the weekend and yesterday, I've tried several types of subquery; in the SELECT and as part of the query body itself, but with no luck. Any ideas?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Forbes View Post
    Any ideas?
    sorry, no

    i never did understand what you were trying to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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