Getting two different names - 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

General SQL Server Forums

New to SQL Server Programming

Getting two different names

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2009-01-23 : 08:32:17

Hi Friends

I need two names from the single table.
Here i will explain with tables.I had two tables
First table name Connections .It has fields
ConId userid FEmail REmail
1 2 kotti@gmail.com sam@gmail.com
2 3 Dhina@gmail.com kotti@gmail.com
3 1 Sam@gmail.com Dhina@gmail.com

Second Table Name Member .It has fields
Id Name Email
1 Sambath Sam@gmail.com
2 Kotti kotti@gmail.com
3 Dhinaharan Dhina@gmail.com

here when i use a query like this
select * from connections where (FEmail=kotti@gmail.com or REmail=kotti@gmail.com)

I will get result like This

1 2 kotti@gmail.com sam@gmail.com
2 3 Dhina@gmail.com kotti@gmail.com

What i need is ,i need the names for the emails Dhina@gmail.com
and sam@gmail.com from member table in a single field like this

Name
Sambath
Dhina

Please help me regarding this

Thanks in Advance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-23 : 08:58:43

Didn't understand the output exactly. Can you explain more.

Kotti
Posting Yak Master

129 Posts

Posted - 2009-01-23 : 09:12:53

Thank you for your reply.

In Connection table
ConId userid FEmail REmail
1 2 kotti@gmail.com sam@gmail.com
2 3 Dhina@gmail.com kotti@gmail.com
3 1 Sam@gmail.com Dhina@gmail.com
From the above table ,i will explain
Kotti@gmail.com is connected with sam@gmail.com
Dhina@gmail.com is connected with kotti@gmail.com
Sam@gmail.com is connected with Dhina@gmail.com

What i need is ,i need names of the member who are all connected with kotti@gmail.com.
From the connection table,we know that kotti@gmail.com is connected with Dhina@gmail.com and
Sam@gmail.com .From the Sam@gmail.com and Dhina@gmail.com ,we can get name from member table as
sambath and Dhinaharan.

If you need the explanation much more ,i will explain .

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 09:17:21

[code]
SELECT m.Name
FROM Connections c
JOIN Member m
ON COALESCE(NULLIF(c.FEmail,@Value),NULLIF(c.REmail,@Value))=m.Email
WHERE (c.FEmail=@Value or c.REmail=@Value)
[/code]
pass a conveininet value for @Value like kotti@gmail.com. also remember to declare it

Kotti
Posting Yak Master

129 Posts

Posted - 2009-01-23 : 09:33:38

Thank you visakh16,now i got the Result.
sakets_2000,i thank you too.

Kotti
Posting Yak Master

129 Posts

Posted - 2009-01-23 : 23:49:00

Hi visakh16

Can i get one more thing from your query.

Here kotti@gmail.com is connected with Dhina@gmail.com and Sam@gmail.com .
I need the count of(Sam@gmail.com ) to whom all Sam@gmail.com is connected and count of(Dhina@gmail.com) to whom all Dhina@gmail.com is connected

Thanks in Advance

onlyforme
Starting Member

25 Posts

Posted - 2009-01-24 : 01:26:24

select count(*) from connections where (FEmail='kotti@gmail.com' or REmail='kotti@gmail.com')

Kotti
Posting Yak Master

129 Posts

Posted - 2009-01-24 : 04:39:48

Hi Friends

Help me regarding the Count.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-24 : 08:37:37

you mean this?


SELECT COUNT(m.Name)
FROM Connections  c
JOIN Member  m
ON COALESCE(NULLIF(c.FEmail,@Value),NULLIF(c.REmail,@Value))=m.Email
WHERE (c.FEmail=@Value or c.REmail=@Value)

Kotti
Posting Yak Master

129 Posts

Posted - 2009-01-24 : 09:30:27

I am getting wrong answer when i use Count(m.Name)

What i need is ,kotti@gmail.com is connected with sam@gmail.com and
dhina@gmail.com .i need who are all connected with sam@gmail.com and Dhina@gmail.com(i,e )i need only count.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-24 : 10:48:00

may be this


SELECT Count(*)
FROM Connections c
JOIN(
SELECT m.Name 
FROM Connections  c
JOIN Member  m
ON COALESCE(NULLIF(c.FEmail,@Value),NULLIF(c.REmail,@Value))=m.Email
WHERE (c.FEmail=@Value or c.REmail=@Value)
)tmp
ON tmp.Name=c.FEmail
OR tmp.Name=c.REmail

Kotti
Posting Yak Master

129 Posts

Posted - 2009-01-26 : 23:09:29

Hi visakh

That query is not working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 23:29:04

then show with example data what value you want

Kotti
Posting Yak Master

129 Posts

Posted - 2009-01-27 : 00:05:01

First table name Connections .It has fields
ConId userid FEmail REmail
1 2 kotti@gmail.com sam@gmail.com
2 3 Dhina@gmail.com kotti@gmail.com
3 1 Sam@gmail.com Dhina@gmail.com

Second Table Name Member .It has fields
Id Name Email
1 Sambath Sam@gmail.com
2 Kotti kotti@gmail.com
3 Dhinaharan Dhina@gmail.com

When i used your query i get the names of the people who are all
connected with kotti@gmail.com.In addition what i need is count of the people who are all connected with sambath and Dhina.

Result:
Name
Sambath 2
Dhina 2

How i get the Result is Kotti is connected with Sambath and Dhina so i get there names.
Here sambath is connected with kotti and Dhina,so i get count as 2
Here Dhina is connected with kotti and Sambath,so i get count as 2

Kotti
Posting Yak Master

129 Posts

Posted - 2009-01-27 : 09:30:08

Hi Friends

Thank you for your help.I got the answer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources