SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  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 Count of rows problem when using inner joins

    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:
    Code:
    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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. #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)
    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. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    If it's just the number of bookings you want:
    Code:
    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')


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
  •