Btukfyl

So I have:

• table1=vehicles


• table2=cars


• table3=motocycle


• table4=trucks...

In table1, I have a column type which references by number one of the above tables (table2 | table3 | table4...). I would like to list all vehicles in table1 and join the table referenced in type; but I find that to be a bit hard the only way I find is to get all vehicles and loop through all and for each get the complementary data from the appropriate table specified in type.

For example:

vehicles:

id(0)------------type(1)---------------name(dacia)



id(1)------------type(2)---------------name(porsche)



id(2)------------type(2)---------------name(hummer)



id(3)------------type(3)---------------name(kongo)



id(4)------------type(3)---------------name(renault)

cars(type 1):

id(0)----------vehicleId(0)-------otherInfo(new)

trucks(type 2):

id(0)----------vehicleId(1)-------otherInfo(used)



id(1)----------vehicleId(2)-------otherInfo(like_new)

motocycle(type 3):

id(0)----------vehicleId(3)-------otherInfo(old)



id(1)----------vehicleId(4)-------otherInfo(very_old)

Query Result:

id(0)------------type(1)---------------name(dacia)-------otherInfo(new)



id(1)------------type(2)---------------name(porsche)-------otherInfo(used)



id(2)------------type(2)---------------name(hummer)-------otherInfo(like_new)



id(3)------------type(3)---------------name(kongo)-------otherInfo(old)



id(4)------------type(3)---------------name(renault)-------otherInfo(very_old)

You could use LEFT JOIN clauses that include a condition on the vehicule type, and then use CONCAT/COALESCE to return the (only) matching value, like so :

select

    v.id,

    v.type,

    v.name,

    concat( 

        coalesce( c.otherInfo, ''), 

        coalesce( t.otherInfo, ''), 

        coalesce( m.otherInfo, '') 

    ) as otherInfo

from vehicules v

left join cars      c on c.vehiculeId = v.id and v.type = 1

left join trucks    t on t.vehiculeId = v.id and v.type = 2

left join motocycle m on m.vehiculeId = v.id and v.type = 3

I think you need to read this first : Associations

This is how you can define the relations b/w two models/tables , like

• BelongsTo


• Belongs-To-Many


• HasOne


• hasMany

Once you are done with this, then you can query the model to get realted data as you are expecting. example

// Find all projects with a least one task where task.state === project.state

Project.findAll({

    include: [{

        model: Task

        // you can go nested too , as per your requirment        

    }]

})

This is how you should be doing when you are using the sequelize.js