Cyber Space Cyber Space - 2 years ago 101
SQL Question

Join query using 5 different tables

v_id v_no v_make
1 Bus-01 Tata
2 Bus-02 Volvo
3 Bus-03 Tatra

r_id r_no r_from r_to
1 01 A B
2 02 A C
3 03 A D

vrm_id r_id v_id
1 1 2
2 2 1

d_id d_code d_name
1 drv-1 John
2 drv-2 Ram
3 drv-3 Gomesh

vem_id v_id d_id
1 1 2
2 2 3

I have 5 tables as shown above. Using some kind of join query I want the Output to be as shown below. Since 5 different tables are involved; I am greatly confused as how the desired output can be reached. Every time I sit down to write the join query, everything gets mixed up in my head and I get confused and unable to write join query. I would be highly obliged to anybody who would point me in the right direction perhaps with some sample sql query. Thanks in advance.

Required Output

route v_no driver_name
A to B Bus-02 Gomesh
A to C Bus-01 Ram

The following below i have tried so far

(SELECT b.d_name FROM tbl_veh_driver_map a, tbl_driver_mst b WHERE a.d_id = b.d_id) AS DRIVER_NAME,
(SELECT DISTINCT(b.r_from || ' - ' || b.r_to) AS RT FROM tbl_veh_driver_map a,tbl_route_mst b,tbl_veh_route_map c WHERE a.v_id = c.v_id AND c.r_id = b.r_id) AS ROUTE,
(SELECT a.v_no FROM tbl_vehicle_mst a,tbl_veh_driver_map b WHERE a.v_id = b.v_id) AS v_no,
FROM tbl_veh_driver_map

jpw jpw
Answer Source

Unless I missed something this looks like a simple set on joins on the pk/fk pairs. The query below should give you the result you want:

  r_from || ' - ' || r_to as route, 
  d_name as driver_name
from tbl_route_mst as r
join tbl_vehicle_route_map as rm on rm.r_id = r.r_id
join tbl_vehicle_mst as v on v.v_id = rm.v_id
join tbl_veh_driver_map as vd on vd.v_id = v.v_id
join tbl_driver_mst as d on d.d_id = vd.d_id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download