SQL query over 3 tables at once

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query over 3 tables at once

Author

Topic

thomas49th
Starting Member

1 Post

Posted - 2010-08-05 : 15:52:29

Hi, I have three tables

table_1, table_2, table_3

table_1 has field

ID, CID, Name

There can be CID to Name is many to many

table_2 has fields
ID, CID, Type

likewise CID to Type is many to many

table_3 has fields
Type, Solution

The last table is one to one. So one type has one solution

Now what I want to do is input a name and get out all the solutions

So say I input Tom

In table_1 we have the data:
CID, Name
1, Tom
1, Pete
2, Tom

So I grab the CIDs from Tom which are 1,2 and stick that

In table_2:
CID, Type
1, X
1, Z
2, Y

(I ignored ID as that's there for primary key)

So putting type into table_3:
Type, Solution
X, Nothing
Y, Return
Z, Jump

So output would be Nothing, Return

How do I make that into a query form though

I tried:

"SELECT table_3.Solution
FROM table_1, table_2, table_3
WHERE table_1.name='$name'
AND table_2.CID = table_1.CID
AND table_3.Type = table_2.Type"

but that doesn't work

Any ideas?
Thanks
Thomas

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-05 : 15:57:22

[code]SELECT Solution
FROM table_1 t1
JOIN table_2 t2
On t1.CID = t1.CID
JOIN table_3 t3
On t3.Type = t2.Type
WHERE t1.Name = 'Tom'[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources