[thelist] SQL help - I think JOINing is required

[Warden, Matt]

On Nov 11, Marc Seyon had something to say about [thelist] SQL help - I...

>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* I understand your setup right, you want something like this:

SELECT g.foo, c.bar, j.silly, e.string
FROM GeneralInfo g, Contact c, Job j, Education e
WHERE g.ID=c.GenID
AND g.ID=j.GenID
AND g.ID=e.GenID
AND g.ID=[the id]

You don't have to alias them like that (in fact, many people will tell you
not to), but I'm lazy.

Now, since these are in different tables, you really don't have a 1-1
relationship here. IOW, an general info record can have many contact, job,
and education records. If that's the case, the above ain't going to work
for you and it get's a little nastier.

--
mattwarden
mattwarden.com