Simple Query - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Simple Query

Author

Topic

ofsouto
Starting Member

22 Posts

Posted - 2005-05-17 : 09:55:54

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.

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-17 : 10:43:30

Try like this

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 and par.class_code = 1

With Regards
Sreenivas Reddy B

ofsouto
Starting Member

22 Posts

Posted - 2005-05-17 : 11:17:47

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources