Need help with distinct function

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

General SQL Server Forums

New to SQL Server Programming

Need help with distinct function

Author

Topic

mjoshi
Starting Member

9 Posts

Posted - 2011-10-10 : 10:16:40

Hi,

I have a table which has many to many relationship. Though in my result I only want one to one relationship, i.e. I want for a particular query whichever contition matches first in "WHERE" clause get displayed.

But while trying with the distinct function too, I am getting multiple rows.

Please help me in resolving this.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 10:23:08

it will be easier for us to help you if you can post your table schema, sample data and required result

KH
[spoiler]Time is always against us[/spoiler]

mjoshi
Starting Member

9 Posts

Posted - 2011-10-10 : 10:32:19

Here is the code which I am using.

select distinct p.description, e.name from phase p , env e , phases_envs pe where pe.phase_id = p.phase_id and (e.name like 'ASD%' or e.name like 'SOA%')

Here the table phases_envs has two columns phase_id and env_id which has many to many relationship.(i.e. a environment can exists in multiple phases, and a phase can have multiple environments)

Table phase has a column name as phase_id, and env table has a column name as env_id.

The table phases_env exists as a connection between these two tables.

But for my result I want that one environment should have a single phase.i.e only one phase_id for a env_id.

Please help!!!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-10 : 10:36:56

>> I want for a particular query whichever contition matches first in "WHERE" clause get displayed
What do you mean by first?

select * from
(
select distinct p.description, e.name ,
seq = row_number over (partition by e.name order by p.description)
from phase p , env e , phases_envs pe where pe.phase_id = p.phase_id and (e.name like 'ASD%' or e.name like 'SOA%')
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 10:41:45

quote:
select distinct p.description, e.name from phase p , env e , phases_envs pe where pe.phase_id = p.phase_id and (e.name like 'ASD%' or e.name like 'SOA%')

what is the relationship between table "env" and "phases_envs" ? I don't see any join condition between these 2 tables

KH
[spoiler]Time is always against us[/spoiler]

mjoshi
Starting Member

9 Posts

Posted - 2011-10-10 : 10:48:55

There is no relationship between phase and env table,
These two table has phase_id and env_id column respectively, which are related in phases_envs table.

mjoshi
Starting Member

9 Posts

Posted - 2011-10-10 : 10:53:03

Thanks Nigelrivett for your time, but this query isn't working, it is giving error in line "seq = row....."
I guess it is giving error for "=" operator.

Are there any changes which I have to make in this query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 10:53:28

in that case, try adding it in where clause


AND pe.env_id = e.env_id

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-11 : 00:53:12

try


select e.name as ENV_NAME , min(p.description) as PHASE_NAME 
from   phase p , env e , phases_envs pe 
where  pe.phase_id = p.phase_id and pe.env_id = e.env_id and (e.name like 'ASD%' or e.name like 'SOA%')
group by e.name

you have just deleted a post ?

KH
[spoiler]Time is always against us[/spoiler]

mjoshi
Starting Member

9 Posts

Posted - 2011-10-11 : 00:54:49

Okay Friends, I tried this query -

select distinct e.name as ENV_NAME , p.description as PHASE_NAME from phase p , env e , phases_envs pe where pe.phase_id = p.phase_id and pe.env_id = e.env_id and (e.name like 'ASD%' or e.name like 'SOA%')

Output -

ENV_NAME | Phase_name
--------------------------
SOAST1 | CW3
SOAST1 | SOABUILD
SOAST1 | SOA_IB
SOAST1 | SOA_MR
SOAST2 | SOABUILD
SOAST2 | SOA_R1
SOAST2 | CW3
ASDAT01 | ASDBUILD
ASDAT01 | ASD_P1

and so on.....

So still I am getting same env_names multiple times... which is the problem, as I only want unique env_name in the output.

SO In my output, I want something like :

ENV_NAME | Phase_name
--------------------------
SOAST1 | CW3
SOAST2 | SOABUILD
ASDAT01 | ASDBUILD
ASDPT | ASD_P1

and so on... i.e. only one matching condition.

Please provide some help!!!

mjoshi
Starting Member

9 Posts

Posted - 2011-10-11 : 00:59:31

Wow!!! SO finally I got the output right.

Thanks a ton Khtan...

The query worked :)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-11 : 04:07:09

That should have been row_number() - I missed off the brackets.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources