How to do inner join in associative table? [closed]

1

Tables and inserts:

CREATE TABLE CLIENTE(
  IDCLIENTE INT PRIMARY KEY AUTO_INCREMENT,
  NOME VARCHAR(30) NOT NULL,
  SEXO ENUM('M','F') NOT NULL,
  ID_CARRO INT UNIQUE
);

INSERT INTO CLIENTE VALUES (NULL,'GABRIEL','M',1);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL2','M',2);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL3','M',3);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL4','M',4);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL5','M',5);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL6','M',6);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL7','M',7);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL8','M',8);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL9','M',9);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL10','M',10);

CREATE TABLE TELEFONE(
  IDTELEFONE INT PRIMARY KEY AUTO_INCREMENT,
  TIPO ENUM('RESIDENCIAL','CELULAR') NOT NULL,
  NUMERO VARCHAR(15) NOT NULL,
  ID_CLIENTE INT
);

CREATE TABLE MARCA(
  IDMARCA INT PRIMARY KEY AUTO_INCREMENT,
  MARCA VARCHAR(30) NOT NULL UNIQUE
);

INSERT INTO MARCA VALUES (NULL,'FIAT'); -- 1
INSERT INTO MARCA VALUES (NULL,'VOLKSWAGEM'); -- 2
INSERT INTO MARCA VALUES (NULL,'TOYOTA'); -- 3
INSERT INTO MARCA VALUES (NULL,'RENAULT'); -- 4
INSERT INTO MARCA VALUES (NULL,'LAMBORGHINI'); -- 5

CREATE TABLE CARRO(
  IDCARRO INT PRIMARY KEY AUTO_INCREMENT,
  MODELO VARCHAR(30) NOT NULL,
  PLACA VARCHAR(30) NOT NULL UNIQUE,
  ID_MARCA INT 
);

INSERT INTO CARRO VALUES (NULL,'PALIO','CSW-6202',1);
INSERT INTO CARRO VALUES (NULL,'PALIO FIRE','AAA-0000',1);
INSERT INTO CARRO VALUES (NULL,'ONIX','BBB-1111',2);
INSERT INTO CARRO VALUES (NULL,'ONIX FIRE','CCC-2222',2);
INSERT INTO CARRO VALUES (NULL,'SPIN','DDD-4444',3);
INSERT INTO CARRO VALUES (NULL,'SPIN FIRE','EEE-5555',3);
INSERT INTO CARRO VALUES (NULL,'SANDERO','FFF-6666',1);
INSERT INTO CARRO VALUES (NULL,'SANDERO FIRE','GGG-7777',1);
INSERT INTO CARRO VALUES (NULL,'GALLIARDO','HHH-8888',1);
INSERT INTO CARRO VALUES (NULL,'GALLIARDO FIRE','III-9999',1);

CREATE TABLE COR(
  IDCOR INT PRIMARY KEY AUTO_INCREMENT,
  COR VARCHAR(30) NOT NULL UNIQUE
);

INSERT INTO COR VALUES (NULL,'CINZA'); -- 1
INSERT INTO COR VALUES (NULL,'VERMELHO'); -- 2

CREATE TABLE CARRO_COR(
  ID_CARRO INT,
  ID_COR INT,
  PRIMARY KEY (ID_CARRO,ID_COR)
);

INSERT INTO CARRO_COR VALUES (1,1);
INSERT INTO CARRO_COR VALUES (1,2);
INSERT INTO CARRO_COR VALUES (2,1);
INSERT INTO CARRO_COR VALUES (2,2);
INSERT INTO CARRO_COR VALUES (3,1);
INSERT INTO CARRO_COR VALUES (3,2);
INSERT INTO CARRO_COR VALUES (4,1);
INSERT INTO CARRO_COR VALUES (4,2);
INSERT INTO CARRO_COR VALUES (5,1);
INSERT INTO CARRO_COR VALUES (5,2);

FOREIGN KEY AND CONSTRAINTS

-- FOREIGN KEY E CONSTRAINTS

ALTER TABLE TELEFONE 
ADD CONSTRAINT FK_TELEFONE_CLIENTE
FOREIGN KEY (ID_CLIENTE)
REFERENCES CLIENTE(IDCLIENTE);

ALTER TABLE CLIENTE
ADD CONSTRAINT FK_CLIENTE_CARRO
FOREIGN KEY (ID_CARRO)
REFERENCES CARRO(IDCARRO);

ALTER TABLE CARRO
ADD CONSTRAINT FK_CARRO_MARCA
FOREIGN KEY (ID_MARCA)
REFERENCES MARCA(IDMARCA);

ALTER TABLE CARRO_COR
ADD CONSTRAINT FK_COR
FOREIGN KEY (ID_COR)
REFERENCES COR(IDCOR);

ALTER TABLE CARRO_COR
ADD CONSTRAINT FK_CARRO
FOREIGN KEY (ID_CARRO)
REFERENCES CARRO(IDCARRO);

* QUERY: *

SELECT C.NOME, CAR.MODELO, CO.COR,
FROM CLIENTE C 
INNER JOIN CARRO CAR 
ON CAR.IDCARRO = C.ID_CARRO;
INNER JOIN CARRO_COR CC
ON CC.ID_CARRO = CAR.IDCARRO
INNER JOIN COR CO
ON CO.IDCOR = CC.ID_COR;

I'm having syntax error, can you help me with this?

    
asked by anonymous 19.09.2017 / 02:42

1 answer

2

You have a comma left over after the columns are declared:

SELECT C.NOME,
       CAR.MODELO,
       CO.COR
  FROM CLIENTE C
       INNER JOIN CARRO CAR ON CAR.IDCARRO = C.ID_CARRO
       INNER JOIN CARRO_COR CC ON CC.ID_CARRO = CAR.IDCARRO
       INNER JOIN COR CO ON CO.IDCOR = CC.ID_COR
    
19.09.2017 / 03:05