I have a database that I'm having trouble filtering using values from a table that does not have a direct relationship with my main table.
My database is roughly as follows
table_main
+----+------------+--------+-------+-------+
| id | date | veh_id | dep | arr |
+----+------------+--------+-------+-------+
| 1 | 2020-01-01 | 1 | 00:00 | 10:00 |
+----+------------+--------+-------+-------+
table_vehicles
+----+------------+-----------------+
| id | reg_number | vehicle_type_id |
+----+------------+-----------------+
| 1 | 10 | 1 |
+----+------------+-----------------+
| 2 | 11 | 1 |
+----+------------+-----------------+
| 3 | 12 | 1 |
+----+------------+-----------------+
| 4 | 13 | 2 |
+----+------------+-----------------+
table_vehicle_types
+----+-------+--------+
| id | make | model |
+----+-------+--------+
| 1 | Chevy | Impala |
+----+-------+--------+
| 2 | Ford | Fusion |
+----+-------+------- +
table_vehicle_types is linked to table_vehicles, and table_vehicles is linked to table_main
What I want to be able to do is display my table_main in a select query and filter it to show a specific vehicle type, for example I want to see all trips made by Impalas when I query table_main, however I do not know how to go about that.
Any help with this would be greatly appreciated, thank you
You can get started with this query. There will need to be a JOIN used to relate the tables to one another as you can see below.
select t1.*
from table_main t1
join table_vehicles t2
on t2.id = t1.veh_id
left join table_vehicle_types t3
on t3.id = t2.vehicle_type_id
where t3.model = 'Impala'