SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  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 exclude from, not include.

    Hi guys!

    I've written a query to perform a search. One of the options is to filter by bookings_history, which I've emboldened below.

    Any given booking can have one or more booking history items, so each booking has at least one booking history item.

    The intention of this option is that when a booking history label type is selected, only bookings with those booking history items associated with them are to be included in the results.

    However, rather than exclude from the results, it's kind of including them via the counter query I'm using. I suspect a subquery or GROUP BY on the counter query may be in order, but I just can't figure this one out, I'm afraid.

    The following is the two queries...

    Code:
    SELECT
    
        COUNT(bookings.booking_id) AS count
    Code:
    SELECT
    
        bookings.booking_id,
        DATE_FORMAT(bookings.creation_date, '%a %D %b %y'),
        DATE_FORMAT(bookings.modification_date, '%a %D %b %y'),
        bookings.status,
        bookings.venue_rates,
        bookings.client_id,
        bookings_attendees.name,
        bookings_attendees.town_city,
        DATE_FORMAT(bookings_attendees.stay_date, '%a %D %b %y'),
        bookings_attendees.stay_nights,
        bookings_attendees.options,
        venues.venue_id,
        venues.name AS venue,
        venues.town_city AS venue_town_city
    And they both share the following...

    Code:
    FROM
        bookings_attendees
        INNER JOIN
        bookings
            ON (bookings.booking_id = bookings_attendees.booking_id)
            AND (bookings.client_id = '3')
    
        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)
            AND (bookings_attendees.name LIKE ('%John%'))
            AND (venues.venue_id = '74')
            AND (bookings.status = 'confirmed')
    
        INNER JOIN
        bookings_history
            ON (bookings_history.label = '1')
            AND (bookings_history.booking_id = bookings.booking_id)
            AND (bookings_history.client_id = bookings.client_id)
            AND (bookings.client_id = '3')
    
        GROUP BY bookings.booking_id
        DESC

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first of all, your GROUP BY is seriously wrong

    beyond that, i can't think

    please see http://dev.mysql.com/doc/refman/5.0/...n-columns.html
    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)
    Yeah, I've been trying lots of things and the GROUP BY found its way through, but that only applied to the actual search query and not the count query.


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
  •