SQL join between multiple independent tables-CodePudding

I have the following 4 tables with unique data

Table: Cars

ID  Name    Status
1   Benz    available
2   Toyota  not-available
3   Maruthi null

Table: Bikes

ID  Name      Status
1   Yamaha    available
2   Honda     not-available
3   Hero      null

Table: Buses

ID  Name      Status
1   Benz      available
2   Leyland   not-available
3   Tata      null

The above three tables are connected in a different table for some purpose like

Table: Connections

ID  Entity_id      Entity_type
1   1              car
2   2              car
3   1              bike
4   2              bike
5   1              bus
6   2              bus
7   3              bus

I need to fetch records from the above connections table with status 'available' of each entity.

result example:

ID  Entity_id      Entity_type
1   1              car
3   1              bike
5   1              bus

I tried with joins but had no luck and some docs suggested to have union between 3 individual join queries which I don't want to do.

Thanks

CodePudding user response：

something like this should work:

select distinct connections.*
from connections, cars, bikes, buses 
where
(Entity_type='car' and Entity_id=cars.Id and cars.Status='available') OR
(Entity_type='bike' and Entity_id=bikes.Id and bikes.Status='available') OR
(Entity_type='bus' and Entity_id=buses.Id and buses.Status='available')

CodePudding user response：

Do you want a list of available vehicles? Try this:

select id, entityid, entitytype from (
   select Id,Entity_id,Entity_type
From Connections
inner join Cars on Cars.id = Connections.Entity_id and cars.status='available'

union

select Id,Entity_id,Entity_type
From Connections
inner join Bikes on Bikes.id = Connections.Entity_id and Bikes.status='available'

union

select Id,Entity_id,Entity_type
From Connections
inner join Buses on Buses.id = Connections.Entity_id and Buses.status='available'


) data

CodePudding user response：

first of all, I suggest to you if you can, it is better to merge all 3 tables in one:

table vehicle :

ID  entity_type status
1   car         available
2   bike        not-available
3   bus         null

and your query in this table is so easy and fast:

Select * from vehicle where status like 'available';

but if you have to do this type, so you have two ways:

1- as you mentioned, you have to join every row to individual table and add where condition to status.

2- there is another union way:

select * from Cars where Cars.id In (
       select Entity_id from Connections where Entity_type like 'car'
   ) union
select * from Bikes where Bikes.id In (
       select Entity_id from Connections where Entity_type like 'bike'
   ) union
select * from Buses where Buses.id In (
       select Entity_id from Connections where Entity_type like 'bus'
   );