homepage Welcome to WebmasterWorld Guest from 54.196.18.51
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




msg:3231659
 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]

 

andreasfriedrich




msg:3233690
 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

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.

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