table1:
+-------------+-----------+--------+
| Cust_id | cust_name | car_id |
+-------------+-----------+--------+
| 1 | A | 1 |
| 2 | B | 1 |
| 2 | C | 3 |
+-------------+-----------+--------+
table2:
+-------------+---------+
| car_id | name_car|
+-------------+---------+
| 1 | TOYOTA |
| 2 | HONDA |
+-------------+---------+
how to select the name_car that have two use in table1 like
+-------------+
| name_car |
+-------------+
| TOYOTA |
+-------------+
and how to view like:
+-------------+---------+
| name_car | COUNT |
+-------------+---------+
| TOYOTA | 2 |
| HONDA | 1 |
+-------------+---------+
..
how to select the name_car that have two use in table1
select q.name_car
from (
select t2.name_car,count(t1.car_id) cnt
from table1 t1
join table2 t2 on t1.car_id=t2.car_id
group by t1.name_car
) q
where q.cnt=2
and how to view like:
select t2.name_car,count(t1.car_id)
from table1 t1
join table2 t2 on t1.car_id=t2.car_id
group by t1.name_car