Show all information for wells where the methods of SM 45000F-C Fluoride, EPA 20
ID: 3736109 • Letter: S
Question
Show all information for wells where the methods of SM 45000F-C Fluoride, EPA 200.8 ICP-MS-Metals, or ICPMS-TOTALS EPA 6020A have been used. Do not use multiple OR commands.(SQL)
Below are my tables
employee * employee_id first-name last_name email (0) gender (0) ob A a employee_id a wellid water_sampleA - startdate end-date (o) # sample-id - ph (0) temperature (0) E street (o) E city (o) state (o) s conductivity (o) dissolved_oxygen (o) E zipcode (o) hire-date (0) date time (0) well_id method-id employee_id well # wellid s depth (o) 9 street (o) city (o) - state (o) zipcode (o) method A methodid description -Explanation / Answer
In this case , we have to use Sub queries to get the required information.
First we will get the method_id infromation from method table for which the description is equal to any of "SM 45000F-C Fluoride, EPA 200.8 ICP-MS-Metals, or ICPMS-TOTALS EPA 6020A" using the following query.
"select method_id from method where description IN ('SM 45000F-C Fluoride', 'EPA 200.8 ICP-MS-Metals', 'ICPMS-TOTALS EPA 6020A')"
After getting this method id , we need to get the required well_id information from water_sample table for the above method_ids.
select well_id from water_sample where method_id IN ( ##### method_ids from above query ######)
After getting this well_id , we need to query the well table for getting the complete well information
So the final query is like :
select * from well where well_id IN (select well_id from water_sample where method_id IN (select method_id from method where description IN ('SM 45000F-C Fluoride', 'EPA 200.8 ICP-MS-Metals', 'ICPMS-TOTALS EPA 6020A')))