Ambiguity in selection with multiplly linked tables

Table refers to table which refers to both...

[R1chard]

I'm not sure if I've made myself clear, but I have 3 tables, of which A is a primary key for B and C, and B is a primary key for C. That is, I have companies (A), which are the organisers of events (B), which have speakers/performers (C), and each speaker works for any of the companies in A (often not the one that organises the event). Each company also has an integer column indicating which industry it is in.

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]

[andreasfriedrich]

Access is prompting for two values because it does not know the values of organis.ID and speak.company. You are trying to alias the table names but you need to do it in the table references instead of the field list.

Given three tables

orgs
====
id
name

events
======
id
org_id
speaker_id

speaker
=======
id
name

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.