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? 1 947
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
--
Scott http://www.OdeToCode.com/blogs/scott/
On Thu, 11 Nov 2004 12:17:45 -0600, "Darrel" <no*****@nospam.com>
wrote: 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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: aaron |
last post by:
I have a question about (i think) joining.
If I have a table in a database that has this info:
key - name - favorite
1 - john - 2
2 - judy - 3
3 - joe - 1
the favorite icecream...
|
by: Smythe32 |
last post by:
I have data that looks like the following:
field1 field2
123456 0,2,4,6
234567 1,2,3
345678
(if there are no values in field2, it is implied that they are 0-9)
What I am trying to do is...
|
by: Matt |
last post by:
Hi all, Thank you for taking the time.
I have a database with 45 tables on it. 44 tables are linked to a main
table through a one to one relationship.
My question is, is there no way i can...
|
by: Pasquale |
last post by:
Is the problem with the query below in the SELECT?? Should I be using
PHP to do some of things I am trying to get the SELECT to do?
What is happening is it opens 3 or 4 of the same query/process...
|
by: faceman28208 |
last post by:
Over the past few years I have consulted on six large projects that all
independently arrived at the same moronic design desision: The use of
SQL query classes.
No, I don't mean a class...
|
by: ferraro.joseph |
last post by:
Hi,
I'm querying Salesforce.com via their AJAX toolkit and outputting query
results into a table. Currently, their toolkit does not possess the
ability to do table joins via their structured...
|
by: john |
last post by:
I have table User-App and table App Profile
User-App App Profile
IDuser IDApp IDApp
1 34 34
1 45 45
2 34
2 45
2 90
3 34
|
by: rfuscjr via AccessMonster.com |
last post by:
This is truly bizzare. I have a query that runs for hours in one Access db.
When I import it into another Access db, it runs in minutes. I compacted and
repaired the original, relinked tables...
|
by: Don Li |
last post by:
Hi,
Env: MS SQL Server 2000
DB Info (sorry no DDL nor sample data):
tblA has 147249 rows -- clustered index on pk (one key of
datatype(int)) and
has two clumns, both are being used in joins;...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |