CODEWITHSUNDEEP
sql-server
Ege Bayrak
Ege Bayrak

Reputation: 1199

Sql query to check if a certain value appears more than once in rows

I have table with 5 columns like this

+----+-------------------------+-----------+--------+-----------+
| Id |       CreateDate        | CompanyId | UserId | IsEnabled |
+----+-------------------------+-----------+--------+-----------+
|  1 | 2016-01-02 23:40:46.517 |         1 |      1 |         1 |
|  2 | 2016-01-16 00:07:59.857 |         1 |      2 |         1 |
|  3 | 2016-01-25 15:17:54.420 |         3 |      3 |         1 |
| 25 | 2016-03-07 16:48:39.260 |        24 |     10 |         0 |
| 26 | 2016-03-07 16:48:39.263 |        25 |      2 |         0 |
+----+-------------------------+-----------+--------+-----------+

(thanks http://www.sensefulsolutions.com/2010/10/format-text-as-table.html for ASCII table!)

I'm trying to check if a UserId is recorded for more than one CompanyId's.

So far I managed to check if a UserId happens to appear more than one by using this query

WITH T AS
(
SELECT * ,
        Count(*) OVER (PARTITION BY UserId) as Cnt
        From CompanyUser
)

select Distinct UserId
FROM T
Where Cnt >1

It returns 2 correctly.

Where I'm stuck is, how can I parameterize the UserId and check if an Id is recorded for more than one company.

Upvotes: 0

Views: 146

Answers (1)

user5684647
user5684647

Reputation: 

Declare @UserID as bigint    
Set @UserID = 2

select Distinct Count(CompanyID)
FROM ComapynUser
Where UserId = @UserId

I think this gives you what you need.

Upvotes: 1

Related Questions