How to find relevent Row values

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 find relevent Row values

Author

Topic

umapathy
Starting Member

24 Posts

Posted - 2007-09-06 : 03:46:28

Hi

i have a table namely x which contains two columns movie and actor.
i want to find out movie name in which more than one actor acted in same movie.

Movie Actor

x a
x b
y c
z d

My expected result set is x

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 03:55:21

[code]
SELECT Actor, Movie
FROM MyTable AS T
JOIN
(
SELECT Actor
FROM MyTable
GROPU BY Actor
HAVING COUNT(*) > 1
) AS X
ON X.Actor = T.Actor
[/code]
Kristen

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 03:56:34

Scratch that, that was Actors who have made more than one movie ...

try again:


SELECT Movie
FROM MyTable
GROUP BY Movie
HAVING COUNT(*) > 1

Kristen

umapathy
Starting Member

24 Posts

Posted - 2007-09-06 : 04:32:31

No,

How many person acted in a Same Move?

Thanks
Umapathy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 04:34:36

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 06:03:12

"How many person acted in a Same Move?"

that's not the same as "i want to find out movie name in which more than one actor acted in same movie"

Modifying my code above:


SELECT Movie, [Actors] = COUNT(*)
FROM MyTable
GROUP BY Movie

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources