0

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

evgeniyp92
  • 3
  • 1

1 Answers1

1

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'
etch_45
  • 792
  • 1
  • 6
  • 21