How to change this 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 2005 Forums

Transact-SQL (2005)

How to change this query

Author

Topic

baska123
Yak Posting Veteran

64 Posts

Posted - 2007-02-07 : 18:10:48

How to change the following query:

select
rownum, a.CREATE_DT as Create_date, a.ID as Achievement_ID, ntr.EMPL_ID, e.PREFER_LAST_NAME, e.PREFER_FIRST_NAME
from
CRRS.ACHIEVEMENT a,
crrs.nominator ntr,
crrs.nominee nee,
crrs.empl e

WHERE
a.id = ntr.ACHIEVEMENT_ID and
a.ID = nee.ACHIEVEMENT_ID and
nee.EMPL_ID = ntr.EMPL_ID and
e.ID = ntr.EMPL_ID

to select only the records that have only one nominator and nominee and both nominator and nominee are still the same person

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 18:52:33

try this


select     rownum, a.CREATE_DT as Create_date, a.ID as Achievement_ID, ntr.EMPL_ID, e.PREFER_LAST_NAME, e.PREFER_FIRST_NAME
from       CRRS.ACHIEVEMENT a
inner join crrs.nominator ntr on a.id = ntr.ACHIEVEMENT_ID
inner join crrs.nominee nee   on a.ID = nee.ACHIEVEMENT_ID and nee.EMPL_ID = ntr.EMPL_ID 
inner join crrs.empl e        on e.ID = ntr.EMPL_ID
inner join 
(
    select ACHIEVEMENT_ID
    from   crss.nominator
    group by ACHIEVEMENT
    having count(*) = 1
) o on a.id = o.ACHIEVEMENT_ID

baska123
Yak Posting Veteran

64 Posts

Posted - 2007-02-12 : 14:04:29

Thank you. This might be the answer. I will let you know how it turns out.

baska123
Yak Posting Veteran

64 Posts

Posted - 2007-02-14 : 16:45:22

it looks like the query constraints achievements to have count of one. What I was looking for is to have nominator count = 1, nominee count = 1 and nominator and nominee must be have the same id.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 17:52:42

Please post some sample data and the result that you want.

baska123
Yak Posting Veteran

64 Posts

Posted - 2007-02-16 : 13:11:22

Here is a short description of the 4 tables:
Nominee: id
Achievement_id
Empl_id

Nominator: id
Achievement_id
Empl_id

Achievement: id

Empl: id

Here are 3 different scenarios:

This is the first scenario where we have one nominator and multiple nominees
Nominator empl nominee empl achievement
12345 12 12345 12 1
23456 22 1
69777 23 1

Multiple nominators and multiple nominees
12345 12 69765 33 5
67777 44 55555 34 5

This is the scenario that I am interested in. Choose records that have only one nominator and nominee, and nominator.employee_id = nominee.employee_id

12345 12 12345 12 7

I hope this will help you to solve my problem. I really appreciate your help.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-16 : 13:18:42

[code]select
rownum, a.CREATE_DT as Create_date, a.ID as Achievement_ID, ntr.EMPL_ID, e.PREFER_LAST_NAME, e.PREFER_FIRST_NAME
from
CRRS.ACHIEVEMENT a,
crrs.nominator ntr,
crrs.nominee nee,
crrs.empl e

WHERE
a.id = ntr.ACHIEVEMENT_ID and
a.ID = nee.ACHIEVEMENT_ID and
nee.EMPL_ID = ntr.EMPL_ID and
e.ID = ntr.EMPL_ID

GROUP BY ntr.[id], nee.[id], rownum, a.CREATE_DT as Create_date, a.ID as Achievement_ID, ntr.EMPL_ID, e.PREFER_LAST_NAME, e.PREFER_FIRST_NAME

HAVING count(distinct ntr.[id]) = 1 and count(distinct nee.[id]) = 1[/code]

baska123
Yak Posting Veteran

64 Posts

Posted - 2007-02-16 : 17:55:19

This query is still giving me multiple nominators an nominees.
I am specifically looking for query where there will be only one nominee and nominator and it will be the same person under one achievement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-16 : 19:35:38

Can you please post some sample data for each of the table and then provide the expected result.

baska123
Yak Posting Veteran

64 Posts

Posted - 2007-02-20 : 10:37:52

I did that already in previous post.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-20 : 17:47:59