SQL query from 3 tables

[lcfjoertoft]

I am having difficulty with a SQL query in my project.

I have 3 tables.

T1:
- Date
- ID
- Location_ID

T2:
- Location
- Location_ID

T3:
- ID
- Result

From this I need the T1.Date and T3.Result based on Location in T2.

So my I need a query to compare a known location, get the Location_ID. Then Use that Location_ID to get the Date and ID. Use the ID to get the Result from T3. (Value is a variable with the known Location)

This is what I have tried:

"SELECT T1.Date,T3.Result _
FROM T1,T2,T3 _
WHERE T1.Location_ID = (SELECT T2.Location_ID FROM T2 WHERE T2.Location = '" & Value & "') AND T3.ID = (SELECT T1.ID FROM T1 WHERE T1.Location_ID = (SELECT T2.Location_ID FROM T2 WHERE T2.Location = '" & Value & "'))"

I get the error 'Sub query returns more than 1 value...'
What am I doing wrong?

[G_Waddell]

Hi,

Your query has no joins, look up INNER JOIN and OUTER JOIN in Google. Basically JOIN tells your query how to join the tables together - you can do it by the where clause as you have done but something in your query is causing an ambiguous join…

What database are you using? There is a database forum under Web development bizarrely enough, (Maybe this should be out on its own forum?) which has sub areas for the common Database servers.

Hopefully this will get you started on the right path...

[Netcode]

You can use JOIN keyword to link two or more tables and retrieve data from those tables based on similar columns that exist in those tables or you could make good use of subqueries

[G_Waddell]

You can use JOIN keyword to link two or more tables and retrieve data from those tables based on similar columns that exist in those tables or you could make good use of subqueries

Err.... sorry isn't that what I more or less told them....

[Netcode]

See if this works:

SELECT T1.date, T3.Result 
FROM T1 
INNER JOIN T3 ON  
T1.ID = T3.ID
WHERE Location = (SELECT Location FROM T2
			WHERE Location ID = 'condition')

[lcfjoertoft]

Got it working, but wil read up on JOIN to maybe improve the code.

"SELECT T1.Date,T3.Result FROM T1,T2,T3 WHERE T1.Location_ID = T2.Location_ID AND T1.ID = T3.ID AND T2.Location = '" & Value & "'"

1st and 2nd comparison after WHERE Links the table.
3rd sets the condition.

[Netcode]

Good one but read up on JOIN as you said, it would save you some time.