CODEWITHSUNDEEP
sqlmany-to-many
Jesus Hernandez Barrios
Jesus Hernandez Barrios

Reputation: 422

Find tuple with only one match in many to many relation

O have an MySQL database with a 'client' table:

id_client (primary)

name_cliet (Unique)

An 'account' tablet with:

id_account (primary)

name_account (Unique)

And a many to many 'client_account':

id_client_account (primary)

id_client (fk for user)

id_account (fk for account)

I need to: Find * From client where ..... I need help in where condition, i need clients with have at least one account and this account only have this cliente. Example: Client table with example data:

1,John

2,Alex

3,Steve

Account table with example data:

1,savings

2,bank

3,school

Client_Account table with example data:

1,1,1 ('John' have a 'saving' account)

1,2,3 ('alex' have a 'school' account)

1,3,1 ('steve' have a 'saving' account)

Need the SQL to show:

2,Alex

Because it's the only cliente with at least one account and the account's only owner

Upvotes: 0

Views: 420

Answers (1)

sticky bit
sticky bit

Reputation: 37487

Use a join, then GROUP BY the client and use a HAVING clause to check for count(*) being exactly one.

SELECT c.id_client,
       c.name_client
       FROM client c
            INNER JOIN client_account ca
                       ON ca.id_client = c.id_client
            INNER JOIN account a
                       ON a.id_account = ca.id_account
       GROUP BY c.id_client,
                c.name_client
       HAVING count(*) = 1;

Upvotes: 1

Related Questions