SQL Join with 3 Tables and WHERE

January 6, 2023

i finish off make this exercise but i wanna more opinion about this final result, if you make other way or tips to improve me current code.

This picture abelow is the explanation about this question.

-- create a table
CREATE TABLE supplier (
  SUPPLIER_CODE TEXT PRIMARY KEY,
  SUPPLIER_NAME TEXT NOT NULL,
  CITY TEXT NOT NULL
);

CREATE TABLE part (
  CODE_PART TEXT PRIMARY KEY,
  NAME_PART TEXT NOT NULL,
  PRICE TEXT NOT NULL
);

CREATE TABLE car (
  CODE_CAR TEXT PRIMARY KEY,
  NAME_CAR TEXT NOT NULL,
  TYPE TEXT NOT NULL
);

CREATE TABLE supply (
  CODE_SUPPLIER TEXT PRIMARY KEY,
  CODE_PIECE TEXT NOT NULL,
  CODE_CAR TEXT NOT NULL
);

INSERT INTO supplier VALUES ('S1', 'Auto peças', 'Camacan');
INSERT INTO supplier VALUES ('S2', 'Peças FTX', 'VITORIA');
INSERT INTO supplier VALUES ('S3', 'Importados AUTO', 'VITORIA');

INSERT INTO part VALUES ('P1', 'MOTOR', '1000');
INSERT INTO part VALUES ('P2', 'VELA', '1500');
INSERT INTO part VALUES ('P3', 'MOTOR', '3000');

INSERT INTO car VALUES ('C1', 'KOMBI', 'HATCH');
INSERT INTO car VALUES ('C2', 'FUSCA', 'HATCH');
INSERT INTO car VALUES ('C3', 'KOMBI', 'HATCH');

INSERT INTO supply VALUES ('S1', 'P2', 'C2');
INSERT INTO supply VALUES ('S2', 'P1', 'C1');
INSERT INTO supply VALUES ('S3', 'P3', 'C3');

-- fetch some values
SELECT  supplier.SUPPLIER_NAME, part.PRICE 
FROM supplier INNER JOIN supply, part, car ON supply.CODE_SUPPLIER = supplier.SUPPLIER_CODE AND supply.CODE_PIECE = part.CODE_PART AND supply.CODE_CAR = car.CODE_CAR 
WHERE (supplier.CITY='VITORIA' AND part.NAME_PART='MOTOR' AND car.NAME_CAR='KOMBI' );

Final result

Peças FTX|1000
Importados AUTO|3000

I use the website, https://www.mycompiler.io/new/sql to test my sql.

>Solution :

Your SQL functions correctly in SQLite.

One critique of your query: You use a combination of JOIN and the old-school supply, part, car comma join syntax. It’s far clearer, at least to 21st-century data people, to use JOIN syntax throughout. And, if you have to change things to use a LEFT JOIN later, it’s less error prone. And, it doesn’t port to most other database server makes.

One style thing: Be fanatic about formatting your queries to be readable. One long line: not readable. It’s important to be able to read and reason about queries, and formatting helps a lot. It’s important for yourself a year from now, and for colleagues.

Here is my rewrite of your query to match my suggestions.

SELECT  supplier.SUPPLIER_NAME, part.PRICE

        FROM supplier
  INNER JOIN supply   ON supply.CODE_SUPPLIER = supplier.SUPPLIER_CODE
  INNER JOIN part     ON supply.CODE_PIECE    = part.CODE_PART
  INNER JOIN car      ON supply.CODE_CAR      = car.CODE_CAR  

 WHERE supplier.CITY='VITORIA'
   AND part.NAME_PART='MOTOR'
   AND car.NAME_CAR='KOMBI';