CODEWITHSUNDEEP
mysqlsql
Othi
Othi

Reputation: 356

SQL - Row of IDs, fetch joined data

I have a table garages structured like this:

garageid | car1 | car2 | car3

The carN fields contain IDs from another table cars:

carN | make | color

Is there a way to get the list of colors for a specific garage in a single query?

The result would be:

garageid | color1 | color2 | color3

I realize this structure is not normalized, but the number of cars will never change. Is there a way to do this?

Upvotes: 0

Views: 46

Answers (2)

Fabrizio
Fabrizio

Reputation: 21

In my opinion is better to have a table t_garage_cars:

garageId carId

In this manner is simple to retrive data.

If this is not possible for some reason I believe you can only use a cursor may be with a temp table

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Storing multiple columns with ids is usually not a good approach. It is better to have one row per car. That said, you can do what you want using multiple joins:

select g.garageid, c1.color as color1, c2.color as color2, c3.color as color3
from garages g left join
     cars c1
     on g.car1 = c1.carid left join
     cars c2
     on g.car2 = c2.carid left join
     cars c3
     on g.car3 = c3.carid;

The left join will still return the garage even when one or more of the car ids are NULL.

Upvotes: 3

Related Questions