CODEWITHSUNDEEP
sqlsql-server-2008
Andha
Andha

Reputation: 917

SQL Query to show rows that only have one value in table

I have problem in writing SQL in SQL Server. Here is the case, I have three tables named User, Client, and UserClient. Below are the sample of the table content

CLIENT

idClient     | client
____________________________
1            | Client A
2            | Client B
3            | Client C

USER

idUser       | User
____________________________
1            | User A
2            | User B
3            | User C

USERCLIENT

idUserClient | idUser     | idClient
____________________________________
1            | 1          | 1
2            | 1          | 2
3            | 2          | 1
4            | 2          | 3
5            | 3          | 1

I need a query to show user with idClient = 1 AND only have one client from USERCLIENT table. As shown in the sample above, the result of the query should be

idUserClient | idUser     | idClient
_________________________________________
5            | 3          | 1

Upvotes: 2

Views: 1607

Answers (5)

Andriy M
Andriy M

Reputation: 77657

This will show users that are associated with the client idClient = 1 and only that client:

SELECT
  MAX(idUserClient) AS idUserClient,
  idUser,
  MAX(idClient) AS idClient
FROM USERCLIENT
GROUP BY idUser
HAVING COUNT(*) = 1
   AND COUNT(CASE idClient WHEN 1 THEN 1 END) = 1

The HAVING clause checks that a group of rows for a user contains exactly one row and among 'them' there's exactly one row where idClient = 1.

Alternatively, SQL Server 2008 would allow you to do something like this:

WITH ranked AS (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (
      PARTITION BY idUser
      ORDER BY CASE idClient WHEN 1 THEN 1 ELSE 0 END, idClient
    )
  FROM USERCLIENT
)
SELECT
  idUserClient,
  idUser,
  idClient
FROM ranked
WHERE rnk = 1
  AND idClient = 1

The CTE ranks the rows in USERCLIENT in such a way that if a user is associated with idClient = 1 and no other client, the corresponding row is ranked as 1, otherwise the row with idClient = 1 (if such one is present) gets a different rank. Therefore, when selecting from the CTE, you simply need to filter on rnk = 1 AND idClient = 1.

Upvotes: 0

Christian
Christian

Reputation: 3972

BINGO!

SELECT * FROM USERCLIENT WHERE iduser IN 
 (SELECT iduser FROM USERCLIENT GROUP BY iduser HAVING COUNT(iduser)=1)

Upvotes: 1

Yves M.
Yves M.

Reputation: 3318

This worked for me...

with cte AS
(
    SELECT 1 AS idUserClient, 1 AS idUser, 1 AS idClient 
    UNION
    SELECT 2 AS idUserClient, 1 AS idUser, 2 AS idClient 
    UNION
    SELECT 3 AS idUserClient, 2 AS idUser, 1 AS idClient 
    UNION
    SELECT 4 AS idUserClient, 2 AS idUser, 3 AS idClient 
    UNION
    SELECT 5 AS idUserClient, 3 AS idUser, 1 AS idClient 
)
SELECT * FROM cte c1
WHERE 
    idUserClient IN 
    (
        SELECT MIN(c2.idUserClient) FROM cte c2 GROUP BY c2.idUser HAVING COUNT(c2.idUser) = 1
    )

Upvotes: 1

Mikael Östberg
Mikael Östberg

Reputation: 17146

Given the data you mention, you can get the result you are looking for in two ways.

select * from userclient where idUser = 3

or

select * form userClient where idUser = 3 and idClient = 1

I'm not sure what you really want, but these two cases produces the result.

What do you got to query with?

Upvotes: 0

Cyril Gandon
Cyril Gandon

Reputation: 17048

Assuming that (idUser, idClient) is an unique index:

SELECT USERCLIENT.idUser
FROM USER
   INNER JOIN USERCLIENT
       ON USER.idUser = USERCLIENT.idUser
WHERE USERCLIENT.idClient = 1
GROUP BY USERCLIENT.idUser
HAVING COUNT(*) = 1

Upvotes: 0

Related Questions