sql get description 2 column on 1 table

Question

I have 2 tables:

car

ID    Desc
1     BMW
2     Audi

user

ID   Name    ID_car1     ID_car2
1    Jack       1           2

Data View

ID    Name   ID_car1   Desc_car1    ID_car2    Desc_car2
1     Jack      1          BMW        2           Audi

How to query to show desc_car1 and desc_car2 on Data view?

you need to nomrmalize your database. here is a link for that. https://stackoverflow.com/questions/3282510/database-normalization — codelearner, Oct 18 '18 at 02:53
sorry data view only for report on php table, how to get this data? — Drajad, Oct 18 '18 at 02:55
I don't see any relationship between the users and the cars. Is there a third table somewhere which stores this information? — Tim Biegeleisen, Oct 18 '18 at 02:57

score 1 · Answer 1 · answered Oct 18 '18 at 03:01

Ideally there should be a third junction table which persists the relationship between users and cars. If you want a literal query to generate the above output in the absence of such a table, then we would have to use a cross join between car and user, because no other relations exist:

SELECT
    u.ID,
    u.Name,
    MAX(CASE WHEN c.ID = 1 THEN c.ID END) AS ID_car1,
    MAX(CASE WHEN c.ID = 1 THEN c.`Desc` END) AS Desc_car1,
    MAX(CASE WHEN c.ID = 2 THEN c.ID END) AS ID_car2,
    MAX(CASE WHEN c.ID = 2 THEN c.`Desc` END) AS Desc_car2
FROM car c
CROSS JOIN user u
GROUP BY
    u.ID,
    u.Name;

Demo

score 0 · Answer 2 · answered Oct 18 '18 at 03:22

0

Using your recent information, we can get the Data View using a query, like so:

select u.ID, u.Name,
  u.ID_car1, c1.Desc Desc_car1,
  u.ID_car2, c2.Desc Desc_car2
from user u
  join car c1 on u.ID_car1=c1.ID
  join car c2 on u.ID_car2=c2.ID;

answered Oct 18 '18 at 03:22

Junjie

491
1
6
13

sql get description 2 column on 1 table

2 Answers2

Demo