Is INNER JOIN usefull here?

Question

CREATE TABLE Masina(    
    idMasina INT PRIMARY KEY NOT NULL,    
    marca VARCHAR(20),     
    model VARCHAR(20),    
    pretFaraDotari DOUBLE
);

CREATE TABLE Dotari(    
    idDotare INT PRIMARY KEY NOT NULL,    
    dotare VARCHAR(30),    
    pret INT
);

CREATE TABLE Comenzi(    
    idComanda INT PRIMARY KEY NOT NULL,    
    idMasina INT,    
    pretTotal DOUBLE,    
    discount VARCHAR(10),    
    FOREIGN KEY (idMasina)
    REFERENCES Masina(idMasina) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Com_Dot(
    idComanda INT,
    idDotare INT,
    FOREIGN KEY (idComanda)
    REFERENCES Comenzi(idComanda) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (idDotare)
    REFERENCES Dotari(idDotare) ON DELETE CASCADE ON UPDATE CASCADE
);

How can i display "marca", "model", "pretFaraDotari" from "Masina" table, the total price of "dotare" for each car from "Dotari" table, "pretFaraDotari" + total price of dotare, and "pretTotal" from the "Comenzi" table for all entries in the table "Comenzi"?

I need all of this displayed using just one command.. that's the big problem

no because I don't know even where to start from.. the problem is that i can't use INNER JOIN from just Masina, Dotari and Comenzi tables, so I must use the Com_Dot table.. but i don't know how.. Also, how can I do that total price for "dotare" for each car from "Dotari" table? — Andrei Tomescu, Dec 11 '17 at 22:08
Use the answer with more points. The accepted answer use old style join — Juan Carlos Oropeza, Dec 11 '17 at 22:11
@AndreiTomescu You'll use your keys, so for `Com_Dot` you'll `JOIN` on either `idComanda`, `idDotare`, or both (depending on how the tables relate). For your totals you will want `SUM(column)` with `GROUP BY othercolumns`. Give the link Juan provided a shot and ask another more specific question if you have issues getting those totals to work. — Aaron Dietz, Dec 11 '17 at 22:25

score 0 · Answer 1 · answered Dec 11 '17 at 22:07

I don't know if I "get" it exactly (I don't speak Romanian) but maybe you're looking for something like

select 
    cd.*,  
    c.*,
    d.*,
    m.*
from Com_Dot as cd,
join Comenzi as c on c.idComanda = cd.idComanda
join Dotari as d on d.idDotare = cd.idDotare
join Masina as m on m.idMasina = c.idMasina

You could add where / group by / having as necessary if you need some sort of aggregate.

Is INNER JOIN usefull here?

1 Answers1