filter out user that belong to 2 country

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)

filter out user that belong to 2 country

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-06-18 : 08:07:28

Hi

I have a table with user information, the table have 4 Columns, UserID, UserName, Company and Country. The data can look like this...


UserID  UserName  Company  Country
1 Mark Smith  Company 1  USA
2 Mark Smith  Company 1  Mexico
3 John Low  Company 1  USA
4 Mark Low  Company 1  Mexico
5 Anna Low  Company 1  Mexico

I need to filter out user that work in diferent countires, but the user must occur more than one time, so based on this all records but record 5 should be displayed from the data above. Beacuse UserID 5 only work at one place (company 1 in Mexico) I have no idea on how to solve this, can someone please help me?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 08:48:34

Try

select * from table
where username in
(
select username from table
group by username
having count(distinct compnay)>1
)

Madhivanan

Failing to plan is Planning to fail

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-06-18 : 09:05:05

Hi

when I run this as a view in sql server management studio, this code is generated...

SELECT ID, UserName, Password, UserRealName, Email, UserLevel
FROM dbo.tbl_Login
WHERE (UserRealName IN
(SELECT UserRealName
FROM dbo.tbl_Login AS tbl_Login_1
GROUP BY UserRealName
HAVING (COUNT(DISTINCT Country) > 1)))

But I also get a error: Invalid column name "Country", why is that? The country column do exsit

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 09:08:05

In whcih table the column is available?

Madhivanan

Failing to plan is Planning to fail

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-06-18 : 09:17:29

Hi

All information/columns is in tbl_Login

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 10:09:46