How to create a SQL query to join 3 tables-CodePudding

I have 3 tables Client, User, UserAssociation:- Need to display ClientName, UserName with respect to UserAssociation

Client

ClientID | Name
   1     | xyz
   2     | abc

User

UserId | UserName
   20  |   Sam
   21  |   Tony

UserAssociation

ClientId | UserId | UserAssociationId
   1     |   20   |         1
   2     |   21   |         2

How to Display

CodePudding user response：

You can do it in this way

SELECT UserAssociationId,
   Name AS ClientName,
   UserName
FROM   userassociation
   INNER JOIN USER
           ON User.userId = userassociation.userId
   INNER JOIN Client
           ON Client.clientId= userassociation.clientId;

CodePudding user response：

You can use the query below with alias, it will produce your desired output :

Select UserAssociationId,UserName as UserName
from UserAssociation UA
inner join User U on U.UserId  = UA.UserId
inner join Client C on C.ClientID = UA.ClientID;

Output on MySQL workbench:

UserAssociationId UserName ClientName

1    Sam     xyz

2    Tony    abc

CodePudding user response：

You may want all the clients regardless if they have a username or not, or the other way. If you want all clients with a Username then you would use an INNER join instead:

Select Client.clientId, Client.Name, User.UserId, User.Username
from Client
left join UserAssociation on UserAssociation.ClientId = Client.ClientId
left join User on UserAssociation.UserId = User.UserId;