-1

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

emanuele pascale
  • 45
  • 1
  • 6
  • Then do not use `*` after select, but specify all fields individually. (and best is to just specify ONLY the field that you actually need.) – Luuk Jun 30 '22 at 16:34
  • Don't use `SELECT *`. Specify the columns you do want. And no, there is no `* except car_cli.Id_Code` – HoneyBadger Jun 30 '22 at 16:36

1 Answers1

1
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');
Sergey Demyanenko
  • 46
  • 2
  • EDIT: I got it, I am selecting all the fields just from client and car instead of all the fields that include also car_client as I was doing, right?. It works, but what is `SELECT Client.*, Car.* FROM`? I'd like to understand what I was doing wrong. Even if you could just link some documentation of what I was missing it'd be great. Thanks :) – emanuele pascale Jun 30 '22 at 17:00
  • 1
    @emanuelepascale * just means all columns. So `Client.*` means all columns from Client. It is bad practice to use *, because if the table gets more columns, it could cause problems for the data-consuming software. See for more examples [here](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) – HoneyBadger Jun 30 '22 at 17:20