May 17, 2005 at 8:52 am
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.
May 17, 2005 at 9:08 am
I don't see why ? Do you have some sample data ?
* Noel
May 17, 2005 at 9:28 am
Try this:
SELECT fun.function_desc
, fun.function_code
FROM table_1 fun
join(select itm.function_code
from table_2 itm
JOIN table_3 par
ON par.parameter_code= itm.parameter_code
WHERE par.class_code = 1
group by itm.function_code
having count(*) = 1
) as P ( function_code )
on fun.function_code = P.function_code
SQL = Scarcely Qualifies as a Language
May 17, 2005 at 9:29 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply