Displaying data from an external relationship in PostgreSQL

Question

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

etch_45 · Accepted Answer · 2020-11-19 16:26:27Z

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'

edited Nov 19, 2020 at 16:26

answered Nov 19, 2020 at 16:19

etch_45

7921 gold badge6 silver badges21 bronze badges

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

1 Comment

user3188140 Over a year ago

can you re-edit the quotes around Impala to be single-tick (')?

Collectives™ on Stack Overflow

Displaying data from an external relationship in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related