[thelist] SQL help - I think JOINing is required

[Damian Maclennan]

Hi Marc

After your FROM clause, you want to use some INNER JOIN clauses.

I.E.

FROM
GenInfo
INNER JOIN Contact ON Contact.Id = GenInfo.Id
INNER JOIN Job ON Job.Id = GenInfo.Id
INNER JOIN Education ON Education.Id = Education.Id

Some RDBMS systems require joins as you described it with the conditions
in the Where clause.
 I.E.
 From GenInfo, Contact, Job, Education
WHERE
Contact.Id = GenInfo.Id and
 Job.Id = GenInfo.Id and
Education.Id = Education.Id

But that is not ANSI syntax and is being depreciated. AFAIK MS Access
will only take the first option. MS SQL will do both, but it is better
to stick to the INNER JOIN syntax.

Hope that helps

Damian

-----Original Message-----
From: Marc Seyon [mailto:seyon at delime.com] 
Sent: Monday, November 12, 2001 2:03 PM
To: thelist at lists.evolt.org
Subject: [thelist] SQL help - I think JOINing is required


Evening all, I'm looking for some help from some of you guys who 
undoubtedly know way more about SQL than I do. I'm afraid I'm a bit out
of 
my depth here.

Here's the situation.

Four tables:
General Info - Name, etc and a unique ID,
Contact, Job, Education.

In each of the latter three, the entry is referenced by the same unique
ID 
stored in General Info. So, for any given record, GenInfo.ID =
Contact.ID = 
Job.ID = Education.ID

In a nutshell, I'm trying to select everything from each table for an 
entry, based on the Name. I've got as far as SELECT TableName.ColumnName

(for each item) FROM List of tables. Not sure how the WHERE clause
should 
be constructed.

If it matters, it's an ASP and MS Access system.

Many thanks.
-marc

---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to: http://lists.evolt.org
Workers of the Web, evolt !