Having extra columns in join between 3 tables MySQL

June 30, 2022

I have 3 tables : Car, Client and Car_client the latter is a junction table between car and client, since as I said Car_Client is a junction table this means the relationship between car and client is many to many. Tables have this structure:

_____________________    ___________________     ______________________
|       Car         |    |     Client      |     |  Car_Client        |
|___________________|    |_________________|     |____________________|  
| license_plate (PK)|    |  Name           |     | license_plate(fk)  |
|     Model         |    |  Surname        |     |  Id_Code(fk)       |
|___________________|    |  Id_Code (PK)   |     |____________________|
                         |  Phone          |
                         |_________________|

Now, what I am trying to achieve is to allow the user to research the client by name. When the name is searched I want to show all the info about the car he owns and his personal info. So basically like a select * from Car, Client but just for a specific name.To achieve this I tried different methods, the one that works best, although not perfectly is this one :

SELECT * FROM
  Car_Client car_cli
  INNER JOIN Car ON ( car_cli.license_plate = car.license_plate)
  INNER JOIN Client ON ( car_cli.Id_Code = Client.Id_Code)
WHERE
  Car_Client.Id_Code in (SELECT Id_Code FROM Client WHERE Name = 'emanuele');

The problem with this solution is that I have two extra columns of license_plate and Id_Code

>Solution :

SELECT Client.*, Car.* FROM
  Car_Client car_cli
  INNER JOIN Car ON ( car_cli.license_plate = car.license_plate)
  INNER JOIN Client ON ( car_cli.Id_Code = Client.Id_Code)
WHERE
  Car_Client.Id_Code in (SELECT Id_Code FROM Client WHERE Name = 'emanuele');