Joining 3 tables 1

[lck092580]

Hi,

I currently have 3 tables:

Table1
ID

Table2
ID
Table1_ID
Table3_ID

Table3
ID

Basically what I want to do is Join all three tables together.

I'm trying to do a:

SELECT Table3.*
FROM Table3 LEFT JOIN Table2 ON Table3.ID = Table2.Table3_ID JOIN Table1 ON Table2.Table1_ID = Table1.ID;

I can't get this to work and I've been looking around and I found:
---
FROM table1 join table2
ON table1.primarykey = table2.foreignkey join table3
ON table2.primarykey = table3.foreignkey
----
Does my query not work because I'm trying to use Table2's FK to join Table1's PK?

The site I found says I should do the reverse by joining Table2's PK to Table1's FK.

Basically what I'm trying to do is to list all items of table3, then join a few items in table2 and finally list a few items from table1 that matches up with table2.

Sorry if this is confusing but I haven't done this for awhile. THanks.

 

[bborissov]

No problems with joining 3 tables. But I am not sure what you want to do. How you will join just a FEW items to Table2? Can you post some data for all tables and deseired result?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[r937]

there's not much point joining Table3 to Table2 with LEFT OUTER JOIN if all you SELECT is Table3 columns...

;-)

SELECT Table3.*
     , Table1.*
  FROM Table3 
LEFT 
  JOIN Table2 
    ON Table2.Table3_ID = Table3.ID
LEFT
  JOIN Table1 
    ON Table1.ID = Table2.Table1_ID

r937.com | rudy.ca

 

[lck092580]

Thanks for the replies. Let me write out the data to make things a bit easier to understand. I think I'm missing something here r937, I keep getting a: "Syntax error (missing operator) in query expression" message.

Update <-- Table Name
ID
Name
KB_Article
Release_Date
Description

Update_Status
ID
Update <-- links to the Update tables ID
Approval_Status
Installed_Date
Server <-- Links to the server ID table below
Update_By <-- Links to the technician table at the very bottom

Server
ID
Name
Label

Technician
ID
FirstName
LastName

Basically what I want to do is have a query that lists all the Servers

JOINED with Updates released by Microsoft..

and finally JOINED with the Update_Status table that displays the status of the servers with each update.

So the result would have repeated Server rows with unique Updates and Update_Status.

For example:

Server Update KB_Article Approval_Stat
Beast Security Update 333333 Approved

etc.

Thanks I really appreciate the help. Haven't done this for awhile.

 

[r937]

see, that's the trouble with using "phony" names, when you get the solution you have to translate it back into your real names, and you get confused -- but don't feel bad, you are not the first person to stumble that way...

SELECT Server.Name as Server
     , Update.Name as Update
     , Update.KB_Article
     , Update_Status.Approval_Status
  FROM Server 
LEFT 
  JOIN Update_Status 
    ON Update_Status.Server = Server.ID
LEFT
  JOIN Update 
    ON Update.ID = Update_Status.Update

r937.com | rudy.ca

 

[lck092580]

Hi r937,

The query you wrote gives me the same error as I was getting yesterday which is this:

Syntax error (missing operator) in query expression 'Update_Status.Server = Server.ID
LEFT
JOIN Update
ON Update.ID = Update_Status.Update'.

I'm using Access to do this if that matters. Thanks for your continuing help. I really appreciate it.

 

[r937]

I'm using Access to do this if that matters.

why yes, it just might do, because this is the SQL Server forum!!!


what are the datatypes of the columns involved?

r937.com | rudy.ca

 

[lck092580]

Hehe... but the underlying structure of SQL is all the same isn't it? I'm not using any trasact SQL commands here and I've always thought that if I stuck with regular JOINs, it should work.

I don't think the other datatypes matter because I'm omitting them in the result. I'm baffled. I refuse to believe Access can't join 3 tables.

 

[r937]

the underlying structure of sql is the same, yes, however there are tons of differences in the various dialects of sql, and access/jet sql is decidely not the same as sql server transact-sql

in particular, the JOIN syntax is not the same when more than two tables are being joined

access/jet sql requires that you provide parentheses for your joins, like this --

SELECT ...
  FROM [COLOR=red][b]([/b][/color]
       Server 
LEFT 
  JOIN Update_Status 
    ON Update_Status.Server = Server.ID
       [COLOR=red][b])[/b][/color]
LEFT
  JOIN Update 
    ON Update.ID = Update_Status.Update

r937.com | rudy.ca

 

[lck092580]

NICE! Thanks r937! It's not returning any errors right now.

However, it's only listed what updates have been assigned to the servers. I want it to include all the updates in the updates table regardless of whether it's been assigned yet.

I think it has something to do with the joins. I'm trying to play around with it right now.

 

[r937]

what does "assigned" mean?

if you want to list "all the updates in the updates table regardless of whether it's been assigned yet" then the updates table should be the left table in the LEFT OUTER JOIN

which is a little different than your first description of the requirement


r937.com | rudy.ca

 

[lck092580]

Hi Rudy,

I originally tried to join the three tables with the Update table first but then realized that not all the servers will be listed. I think I might have done the JOIN incorrectly.

Basically what assigned means is that there's a value in the Update_Status table for the server. The record set being returned right now only includes records in the Update_status table.

What I want is for it to return something like this:

Hotfix something1 Server1 Approved
Hotfix something2 Server1 Approved
Hotfix something1 Server2 Declined
Hotfix something2 Server2 Approved

I'll play around with the joins. Thanks for your continuing help Rudy. I really appreciate it.

 

[r937]

perhaps what you are after is this --

SELECT Update.Name as update
     , Server.Name as server
     , Update_Status.Approval_Status
  FROM (
       Update 
LEFT 
  JOIN Update_Status 
    ON Update_Status.Update = Update.ID
       )
LEFT 
  JOIN Server
    ON Server.ID = Update_Status.Server

r937.com | rudy.ca

 

[lck092580]

Yup! It is. It's not acting the way I want it to though.

I populated the Update table with 61 updates.

I then went into the Server table and populated it with two servers.

If I run the query, it just returns the 61 updates listing with no servers mapped.

Now, if I go into the Update_status table and assign the servers with updates, then rerun the query, I see the listings. However, it's still not listing all the updates with server listings. It only lists what's been assigned in the Update_Status table.

 

[r937]

cannot help you if i cannot see your data

r937.com | rudy.ca

 

[lck092580]

That's ok, you've helped me a lot already Rudy. I'll have to play around with what I have to see whether I can get the results I want. Thanks!