validation based on data from 2 tables

[tksayy]

HI
I have two tables like the following:

table1

id variable
1 speed
2 distance
3 acceleration

table2

id allowedvalue
1 3
1 5
1 6
2 4
2 5


i want to write a a loop to check whether for a given variable in all tables in the database the values is within the allowed value. THe link for getting the allowed values of each table is the id no.

i have tried the following

SELECT id from table1 s where variable = "speed"
inner join table2 on s.id = [table2].id

but i am gewtting missing operator error

hope someone can help

 

[PHV]

The correct syntax is:
SELECT id from table1 s
inner join table2 on s.id = [table2].id
where variable = "speed"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tksayy]

I tried that but i am getting an error message like

"the given field 'id' may be based on more than one of the related tables in the sql statement

 

[PHV]

SELECT [!]s.[/!]id from table1 s
inner join table2 on s.id = [table2].id
where variable = "speed"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tksayy]

now i get as a result of query the id of speed.
1
1
1


any idea how i can proceed to check the values of speed against the allowed values.
do i store the allowed values in a array and run a check?

 

[PHV]

A starting point:
SELECT * FROM yourTable
WHERE speed Not In (SELECT allowedvalue FROM table2 WHERE id=1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886