Welcome to WebmasterWorld Guest from 220.127.116.11
Forum Moderators: open
What I want to do is execute a query which provides all the info from table B (events), as well as a count of how many of the speakers (C) work for companies in a given industry (let's say a constant 1)
I'm testing in Access 2003 for the time being, with the goal to putting it into MySQL. If possible I want to avoid joins as I think they are ugly and clumsy. I currently have the following:
SELECT Events.Title, Company.[Company Name] AS "Organis", People.Surname, Company.[Company Name] AS "Speak"
FROM Events, Company, People
WHERE Events.Organiser = Organis.ID AND
Events.ID = People.Last_speaker AND
Speak.Company = Company.ID
ORDER BY Events.Organiser;
But the second column of this is always listing the same company (also, Access prompts me for two numbers). Obviously once I get the speaker's company and the organiser's company, it will probably be straightforward to get the speaker's industry and count those up for each event, but I seem to be stuck with a syntax error whenever I try to proceed.
Do I just have a bad database design?
Thanks for any help.
[edited by: R1chard at 4:51 pm (utc) on Jan. 25, 2007]
Given three tables
the following query will give you a list of events with their speaker and organization:
SELECT o.name AS OrgName,
e.name AS Event,
s.name AS Speaker
FROM orgs AS o,
speaker AS s,
events AS e
WHERE o.id = e.org_id
AND e.speaker_id = s.id
AND s.id = e.speaker_id;
If there is a many to many relation between speakers and events you should use a cross reference table.
And btw the query you posted is already using joins in your query.