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