homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Ambiguity in selection with multiplly linked tables
Table refers to table which refers to both...

10+ Year Member

Msg#: 3231657 posted 4:48 pm on Jan 25, 2007 (gmt 0)

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]



WebmasterWorld Senior Member 10+ Year Member

Msg#: 3231657 posted 5:29 am on Jan 27, 2007 (gmt 0)

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




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.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved