Can someone help with an SQL query please?

[IanMc2]

Hi all, I'm trying to construct a query but i'm only a beginner at SQL and i'm having
a little difficulty, wondered if there might be someone a little more experienced
who could give me a hand? The database is MySQL.

The tables and columns that i need to get are below, my limited (so far) knowledge
of SQL would allow me to do something like:

SELECT (the columns in the events table), (the columns in the venues table) ,CustomerId
FROM events, venues, bookings
WHERE venues.VenueId = events.VenueId
ORDER BY events.EventDate

Then i get a little lost in the WHERE clause, should it be AND bookings.EventId = events.EventId ?
(I want to get the events even if there are no bookings yet)

tables & columns =

events
.EventId
.VenueId
.EventDate
.EventAgeRangeFemaleFrom
.EventAgeRangeFemaleTo
.EventAgeRangeMaleFrom
.EventAgeRangeMaleTo
.EventStatus
.EventNotes

venues
.VenueId must = events.VenueId
.VenueCapacity
.VenueName
.VenueTown

bookings
.CustomerId
.EventId must = events.EventId

customers
.CustomerId must = bookings.CustomerId
.CustomerGender


These are all the fields that i need to get in my query.
It would seem to be a straightforward query but the complication comes
where for each event there will be a number of customers (hopefully), referred to in the
bookings table. In the customers table there is a column for CustomerGender.
This can be one of two values Male or Female. I need to count how many of each
there are (Male and Female) so that i can stop accepting bookings or say that
there are only a few places left etc.

I know that this works:

SELECT COUNT(CustomerGender) AS MaleCount
FROM customers
WHERE customers.CustomerGender = 'Male';

but i would need two of em' one for Male and one for Female and i'm having trouble
figuring out how to include this in the main events query. Would these be subqueries?

Any help would be much appreciated.

Ian

 

[PHV]

I want to get the events even if there are no bookings yet
Search your documentation for outer join
i would need two of em' one for Male and one for Female
SELECT ....
, (SELECT COUNT(*) FROM customers WHERE CustomerGender='Male') AS MaleCount
, (SELECT COUNT(*) FROM customers WHERE CustomerGender='Female') AS FemaleCount
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rac2]

Oddly enough this counting can be done by recoding and summing. Recoding in SQL is accomplished with a CASE expression.

SELECT SUM(
           CASE
                WHEN CustomerGender = 'Male' THEN 1
                ELSE 0
           END
          ) AS "Guys",
       SUM(
           CASE
                WHEN CustomerGender = 'Female' THEN 1
                ELSE 0
           END
          ) AS "Dolls"
FROM customers

Couple that with JOIN conditions and search conditions in the WHERE clause to get the numbers for a particular event.

SELECT SUM(
           CASE
                WHEN customers.CustomerGender = 'Male' THEN 1
                ELSE 0
           END
          ) AS "Guys",
       SUM(
           CASE
                WHEN customers.CustomerGender = 'Female' THEN 1
                ELSE 0
           END
          ) AS "Dolls"
FROM customers, bookings
WHERE customers.CustomerID = bookings.CustomerID
  AND bookings.EventID = 7

Here the condition that customers.CustomerID = bookings.CustomerID is the JOIN condition and
bookings.EventID = 7 is the search condition.


I like to use the JOIN syntax as it makes this distinction more obvious. While I am at it I will toss in alias notation which can make the whole thing easier to read.

SELECT SUM(
           CASE
                WHEN c.CustomerGender = 'Male' THEN 1
                ELSE 0
           END
          ) AS "Guys",
       SUM(
           CASE
                WHEN c.CustomerGender = 'Female' THEN 1
                ELSE 0
           END
          ) AS "Dolls"
FROM customers c
JOIN bookings b ON 
        b.CustomerID = c.CustomerID
WHERE b.EventID = 7

Here I have defined c as an alias for the table customers.
The condition on the CustomerIDs is clearly part of relating the tables.


Although the above works fine, it is strangely worded because it starts by looking at customers. We would probably think of the problem as looking at the list of bookings for an event and counting the customers. So-

SELECT SUM(
           CASE
                WHEN c.CustomerGender = 'Male' THEN 1
                ELSE 0
           END
          ) AS "Guys",
       SUM(
           CASE
                WHEN c.CustomerGender = 'Female' THEN 1
                ELSE 0
           END
          ) AS "Dolls"
FROM bookings b
JOIN customers c ON 
        c.CustomerID = b.CustomerID
WHERE b.EventID = 7

Hope this helps.

 

[IanMc2]

That looks very interesting thanks!

I shall certainly give it a try.

Ian