Count of rows problem when using inner joins

Databases
1

Hi guys!

I’m trying to get a count of rows, for use in pagination, but all I’m getting is a count of one per result, which I’m sure is an artefact of using INNER JOINs.

I’ve managed to trim the query down to the most essential parts before getting errors:

SELECT
	bookings.booking_id AS id,
	DATE_FORMAT(bookings.creation_date, '%W %D of %M %Y, %H:%i') AS creation_date

FROM
	bookings_attendees
INNER JOIN
	bookings
ON
	(bookings.booking_id = bookings_attendees.booking_id)
AND
	(bookings.client_id = '6')
AND
	(bookings.status = 'pending')
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')
GROUP BY id

It is more than likely the structure of the query itself is to blame in some way (I’m developing a multi-user system, which needs to determine the various users based on their parent client).

I want a COUNT of all rows for bookings.booking_id.

Any ideas?

2

What is it you want to count? And I don’t mean ‘all rows’, I mean something like

'for each booking I want to know the number of attendees ’

or

'for each booking I want to know the number of clients ’

3

Hi, the full query (which I’ve trimmed down for the purposes of just getting a count) retrieves all the bookings that match the criteria in the query, with the additional attendee, client and venue data, but since I’m only concerned with those that match the criteria, I only need to know the number of bookings.

4

If it’s just the number of bookings you want:


SELECT
	COUNT(DISTINCT bookings.booking) AS numberofbookings

FROM
	bookings_attendees
INNER JOIN
	bookings
ON
	(bookings.booking_id = bookings_attendees.booking_id)
AND
	(bookings.client_id = '6')
AND
	(bookings.status = 'pending')
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')