Simple Query

[ofsouto]

I have 3 tables

table_1
function_code
function_desc

table_2
function_code
itemfunction_code
itemfunction_desc
parameter_code

table_3
parameter_code
parameter_desc
class_code

I need to list all functions (code and description - table_1) where the item functions (table_2)
have only parameters with class code = 1

I tried the query bellow but it doesn't work.

SELECT DISTINCT fun.function_desc, fun.function_code
FROM table_1 fun
INNER JOIN table_2 itm
ON fun.function_code = itm.function_code
INNER JOIN table_3 par
ON itm.parameter_code = par.parameter_code
WHERE par.class_code = 1

The query returns all parameters with class code 1 but may return parameters with another classes

Thank you very much.

 

[vongrunt]

I need to list all functions (code and description - table_1) where the item functions (table_2)
have only parameters with class code = 1

To clarify: all item functions or some (any) item functions?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Katy44]

From your query I can't see why - what other class codes are returned? If you add the class code to the select statement, can you show us some sample output?

 

[ofsouto]

I need to list all functions (codes and descriptions) with item functions that have only parameters with class code = 1.

It is wrong if the function has item functions with one parameter with class code = 1 and another parameter with class code = 2.

Thank you very much.

Obede

 

[ofsouto]

The problem was solved:

SELECT DISTINCT fun.function_desc, fun.function_code
FROM table_1 fun
INNER JOIN table_2 itm
ON fun.function_code = itm.function_code
INNER JOIN table_3 par
ON itm.parameter_code = par.parameter_code
WHERE par.class_code = 1
AND fun.function_code NOT IN (
SELECT DISTINCT itm.function_code
FROM table_2 itm
INNER JOIN table_3 par
ON itm.parameter_code = par.parameter_code
WHERE par.class_code <> 1 )


Thank you very much.