CODEWITHSUNDEEP
mysqlsql
Darío Villalta
Darío Villalta

Reputation: 29

SQL Query with nested inner joins and same column name

I've got this 4 tables on sql:

Components

Name, Description, ID

Elements

ID, Name, Description, Component

Practice

ID, Name, Description, Element

Activity

ID, Name, Description, Practice

FinalDate

ID, Activity

and they're related like so:

Component(ID)---->Element(Component)
Element(ID)------>Practice(Element)
Practice(ID)----->Activity(Practice)
Activity(ID)----->FinalDate(Activity)

I want to make a query that for each component, I want to get each activity with its final date.

This is what i currently have

"select componente.Nombre, componente.Descripcion, elemento.* from Componente componente inner join 
(select elemento.Componente, practica.* from Elemento elemento inner join 
    ( select practica.Elemento, actividad.* from Practica practica inner join 
        (select actividad.*, fecha.* from Actividad_FechasFinales fecha inner join 
            (select actividad.Practica, actividad.ID from Actividad) 
        actividad on fecha.Actividad = actividad.ID) 
    actividad on actividad.Practica = practica.ID ) 
practica on practica.Elemento = elemento.ID ) 
elemento on componente.ID = elemento.Componente"

Upvotes: 0

Views: 565

Answers (1)

Nick
Nick

Reputation: 147146

I don't think you need all those subqueries, a simple JOIN should suffice. Note I've assumed there is a Date column in your FinalDate table although you don't mention it in the description:

SELECT c.Name, a.Name, f.Date
FROM components c
JOIN elements e ON e.Component = c.ID
JOIN practice p ON p.Element = e.ID
JOIN activity a ON a.Practive = p.ID
JOIN FinalDate f ON f.Activity = a.ID

Upvotes: 2

Related Questions