Combining a SQL query (joins)

[Darrel]

I'm trying to get the hang of joins.

I'm generating a list from two tables: seminars and attendees.

The list needs to show the seminars, and then it needs to show the number of
attendees signed up.

If they were two queries, I (think) it'd be like this:

SELECT seminarID, seminarTitle, seminarDate FROM seminars

SELECT count(attendeeID) from attendees group by eventID;

That should give me all seminars, and a count of attendees for each seminar.

If I want to return this as one dataset, I then need to do a JOIN, correct?

Here's my stab at it:

SELECT seminarID, seminarTitle, seminarDate, Count(Attendees.AttendeeID) AS
num_atttendees
FROM seminars INNER JOIN Attendees ON seminars.seminarID =
Attendees.seminarID
GROUP BY seminars.EventID

Am I close?

 

[Scott Allen]

Every field in the SELECT list has to be either grouped or aggregated
when you are trying to count. I don't know if this is the exact answer
since I can't see the schema, but the following would seem closer to
what you need:

SELECT
seminarID,
seminarTitle,
seminarDate,
Count(Attendees.AttendeeID) AS num_atttendees

FROM seminars
INNER JOIN Attendees ON
seminars.seminarID = Attendees.seminarID

GROUP BY
seminarID,
seminarTitle,
seminarDate