Joining 3 tables SQL and ACCESS

[dommy]

Hi,

I have the following tables and would like to join them and am a little lost in what seems a maze of join types.

Table1
ID GuysID GirlsID

Table 2
FirstName SurName GirlsID

Table 3
FirstName SurName GuysID

I'd like to join Table 2 and 3, then do: WHERE Table1.ID = (Request.Form"myID")
to prove I'm not a lazy sod, I did give this a go.. with no joy:

"SELECT Table1.*, Table2.* FROM Table1 INNER JOIN Table2 ON Table1.GirlsID = Table2.GirlsID WHERE Table1.ID = " & "'" & rs("myID") & "'" & " UNION ALL SELECT Table1.*, Table3.* FROM Table1 INNER JOIN Table3 ON Table1.GuysID = Table3.GuysID WHERE Table1.ID = " & "'" & rs("myID") & "'"

This is the first time I'm using joins, and the difficult thing here seems to be combining the same table fields (the names in table 2 and 3) but using different ID's.

I'm using SQL and ACCESS.

[Old Pedant]

First of all, *NEVER* use "SELECT *" if you can help it. It's only a bit more work for you, but much better for the server, if you explicitly list the field names you need and *ONLY* those fields. This is especially true with poor little baby Access.

ANYWAY....

I'd like to join Table 2 and 3
???
How. They aren't joinable. Unless you mean you'd like to join them on Surname, perhaps.

What are you after here???

To find all the girls who are matched to guys via Table1? (And PLEASE don't tell us those are your real table names.)

Maybe the big question is: WHY do you have separate tables for Guys and Girls??? It would be *MUCH* cleaner to have
Table: People
personID
surname
firstname
gender


And why are you using rs("myID") instead of Request("myID")?

And what *is* the meaning of the ID field in Table1???

I think it would probably help *TONS* if you would tell us what you are *REALLY* doing, instead of showing this fake data. At least I hope it is fake.

[dommy]

Hi again,

I am only using Select when building the statement - once I have it working, I just pull the fields I need and remove the * - its a decent method to use when developing.

None of the names in the example are real. Its all done to simplify in an effort to make it more easy to follow and for my own security reasons.

The real scenario is this:
I want to pull details from 2 very different tables (table2 and table3 in this simplified example) who happen to have some similar fields ie firstname and surname.. but thats where the similarities end and thus the reason for 2 tables! I have a relationship table in the example named table1 and this stores an ID. What I want to do, is pull the information from table 2 and 3 (names etc) according to a specified ID (thats where rs("myID") comes from). So say I ask for ID = 4 Perhaps there are 2 guys stored with this ID and 1 girl according to table1. It then pulls them together using their personal ID's in the 2 other tables i.e. GuysID or GirlsID and outputs their information as:

Girl: Name

Guy: 'Name of first guy'
Guy: 'Name of second guy'

I don't quite get some of your thoughts as they contradict... you state that you hope my tables aren't named so obvious and lacking meaning and then later ask me to show the real info so you know those aren't real table names? Just a bit confusing, however if this still is not simple enough, I can replace the names with my longer ones, but I only think this will further complicate and I am not trying to make those helping me suffer, I can make the changes.

Appreciate some help. ;)

[Old Pedant]

Well, it was late last night.

What I was really trying to determine is what you want the *results* to look like.

That is, for a given record in Table1, such as

Table1
ID GuysID GirlsID
1 371 293

Do you want to see (final output)
1 371 Joe Schmoe 293 Mary Doe
??

Or did you really want something more like
1 371 'Guy' Joe Schmoe
1 293 'Girl' Mary Doe
as separate records??

[dommy]

Hi Old Pendant,

No worries, just wanted to make sure I was being clear.

The last examples as separate records is the result I'm after. Any light you can shine would be very useful in helping me get to grips with multi table joins.

Thank you!

[Old Pedant]

Well, then, *PROBABLY* something like this:

id = Request("myID")
SQL = "SELECT Table1.ID, 'girls' AS source, Table2.firstname, Table2.surname " _
& " FROM Table1 INNER JOIN Table2 ON Table1.GirlsID = Table2.GirlsID " _
& " WHERE Table1.ID = " & id _
& " UNION " _
& "SELECT Table1.ID, 'boys' AS source, Table3.firstname, Table3.surname " _
& " FROM Table1 INNER JOIN Table3 ON Table1.boysID = Table3.boysID " _
& " WHERE Table1.ID = " & id _
& " ORDER BY ID, source, surname, firstname " ' or whatever
Set RS = conn.Execute( SQL )
DO Until RS.EOF
id = RS("ID")
sourceTable = RS("source")
first = RS("firstname")
surname = RS("surname")
... whatever ...
RS.MoveNext
Loop

You don't have to put "Table1.ID" or "source" into the list of fields selected.

I like to use "source" (or similar) when I create a UNION if it's helpful to know which branch of the UNION produced the record.

So, in short, you had it right really all along. Well, except for putting '...' around an ID value, if the ID is a numeric field in the table. You'd of course put them there if the ID field is actually a text field of some kind.

[Old Pedant]

Note that the ORDER BY in a UNION applies to the complete set of union-ed records, and so you do *not* put an ORDER BY any place except at the end. (There are weird exceptions that require special coding, but that's a 90-10 rule, at least.)

[dommy]

Thank you sir, I'll look through this now. :thumbsup: