Select statement based on multiple table sql-CodePudding

Hi I have the following table

Driver

| Id       | vehicleId     | Power         |
| -------- | --------------|-------------- |
| 123      | 2221          |112            |
| 124      | 2222          |111            |

Vehicle

 vehicleId| VehicleAvl|
| -------- | -------------- |
| 2221| Yes|
| 2222| Yes|

VehicleCountry

Id| vehicleId| VehicleField| VehicleValue |
| -------| -------- | -------------- | -------------- |
| 1 | 2221| Residence Country| USA|
| 2| 2221| Registered Country| USA|
| 3 | 2221| Available Country| CAN|
| 4| 2222| Residence Country| USA|

From the above i Need to write a simple select query which will fetch whehter the vehicle residence country belongs to usa or not

I dont need any columns to be returned rather than simple Select 1

I have only the Driver table ID as input so How can i write a query for the requirement

SELECT 1 
  FROM Driver IES, Vehicle d1,
  
 
where     
 IES.id= :GID and d1.vehicleId= IES.vehicleId
 And (SELECT vehiclevalue AS RESIDENCE_COUNTRY
    FROM  VehicleCountry t2
    where t2.vehicleId=d1.vehicleId
    AND t2.vehiclefield= 'RESIDENCE COUNTRY'
    ) DR 
  AND (IES.POWER IS NOT NULL
  OR DR.RESIDENCE_COUNTRY   != 'USA'
  OR DR.RESIDENCE_COUNTRY   IS NULL)

CodePudding user response：

Something like this? Note that

sample data you posted is poorly formatted so I don't quite understand which value belongs to which column in vehiclecountry table
as both drivers' residence country is USA in your example, I modified the last value to UK, just to make the difference
sample data in lines #1 - 15; query begins at line #17

SQL> with
  2  driver (id, vehicleid) as
  3    (select 123, 2221 from dual union all
  4     select 124, 2222 from dual
  5    ),
  6  vehicle (vehicleid, vehicleavl) as
  7    (select 2221, 'yes' from dual union all
  8     select 2222, 'yes' from dual
  9    ),
 10  vehiclecountry (id, vehicleid, vehiclefield, vehiclevalue) as
 11    (select 1, 2221, 'Residence Country' , 'USA' from dual union all
 12     select 2, 2221, 'Registered Country', 'USA' from dual union all
 13     select 3, 2221, 'Available Country' , 'USA' from dual union all
 14     select 4, 2222, 'Residence Country' , 'UK'  from dual
 15    )
 16  --
 17  select nvl(max(1), 0) result
 18  from driver d join vehicle v on v.vehicleid = d.vehicleid
 19  join vehiclecountry c on c.vehicleid = v.vehicleid
 20  where c.vehiclefield = 'Residence Country'
 21    and c.vehiclevalue = 'USA'
 22    --
 23    and d.id = &par_driver_id;
Enter value for par_driver_id: 123

    RESULT
----------
         1

SQL> /
Enter value for par_driver_id: 124

    RESULT
----------
         0

SQL>

CodePudding user response：

To show the residence country of vehicles for given driver:

select d.vehicleId, v.vehicleValue as residence_country
from driver d
join vehicleCountry vc on vc.vehicleId = d.vehicleId
  and vc.vehicleField = 'Residence Country'
where d.id = $id

Same, but only vehicles whose residence country is USA (so no need to display country):

select d.vehicleId
from driver d
join vehicleCountry vc on vc.vehicleId = d.vehicleId
  and vc.vehicleField = 'Residence Country'
  and vc.vehicleValue = 'USA'
where d.id = $id

CodePudding user response：

SELECT 
  CASE vc.VehicleValue = 'USA' THEN 1 ELSE 0 END
FROM driver d
join vehicleCountry vc on vc.vehicleId = d.vehicleId