MySql Join two table with column names

Question

I have these tables in my MySQL database:

Cars table:

+----Cars   Table-----+
+---------------------+
| id | BMW   | KIA    |-and another cars
+----+----------+-----+
| 1  | M5    | Rio    |
| 2  | Z1    | Serato |
| 3  | X5    | Sorento|

Like table:

+----------------------+-----+
| id | Cars_id| Cars   |ip   |
+----+----------+------+-----+
| 1  |   1    | KIA    |1.0.1|
| 2  |   1    | BMW    |1.0.1|

Basically, the Cars table have more cars Mark and their models.

My question is, how Join this tables - Like.Cars_id=Cars.id and Like.Cars=Cars.Column names?.

So, I would like a query like this:

SELECT Cars.BMW, Cars.KIA,  COUNT(Like.ip) AS likes
FROM CARS_Table
    LEFT JOIN Like_table
        ON Cars.id = Like.Cars_id AND what?

you should normalize your cars table ..

ScaisEdge
– ScaisEdge

2018-08-26 08:24:46 +00:00
Commented Aug 26, 2018 at 8:24 — ScaisEdge
– ScaisEdge, Commented Aug 26, 2018 at 8:24
i want i can count each cars models ip

Ferid Zamanov
– Ferid Zamanov

2018-08-26 08:25:22 +00:00
Commented Aug 26, 2018 at 8:25 — Ferid Zamanov
– Ferid Zamanov, Commented Aug 26, 2018 at 8:25
how i can normalize cars table?

Ferid Zamanov
– Ferid Zamanov

2018-08-26 08:26:37 +00:00
Commented Aug 26, 2018 at 8:26 — Ferid Zamanov
– Ferid Zamanov, Commented Aug 26, 2018 at 8:26

ScaisEdge · Accepted Answer · 2018-08-26 08:32:34Z

1

you should normalize the table this way

Brand 
id, name  
1, BMW 
2, KIA 
3, ...

cars 
id, id_brand, car_name 
1, 1, M5 
2, 1, Z1 
3, 1, X5 
4, 2, Rio 
5, 2, Serato 
6, 2, Sorento 

like_table  

id, cars_id, ip 
1, 1, 1.0.1 
2, 1, 1.0.1 


select b.name, c.name, count(ip)
from  like_table l 
inner join  cars c on l.cars_id = c.id 
inner join  Brand b on b.id = c.id_brand
group by b.name, c.name

in this way is easy get the like for car but also like for brand and others

select b.name count(ip)
from  like_table l 
inner join  cars c on l.cars_id = c.id 
inner join  Brand b on b.id = c.id_brand
group by b.name

answered Aug 26, 2018 at 8:32

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2018-08-26 08:28:42Z

0

if you just want to count cars model ip

then no need join between two tables,below query will count model ip

select Cars,  count(ip) from Like_table    
group by Cars

answered Aug 26, 2018 at 8:28

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Collectives™ on Stack Overflow

MySql Join two table with column names

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related