CODEWITHSUNDEEP
mysqlsql
Haribo
Haribo

Reputation: 2226

combine multiple select statement in SQL

I've just started learning SQL. Here is part of my Database:

enter image description here

I want to get the project name from the Project table with condition:

name = 'turbine' and value = '03' in Parameter table.

I have wrote the following query and it seems to work! But I was wondering if any smarter query can do the job for me :

SELECT name
FROM Project
WHERE id IN (
    SELECT projectId
    FROM Cases
    WHERE id IN (
        SELECT caseId
        FROM ParamValue
        WHERE parameterId IN (SELECT id FROM Parameter WHERE name = 'turbine')
          AND value = '03')
    )
;

Upvotes: 1

Views: 173

Answers (3)

Noureddine
Noureddine

Reputation: 103

select pr.name from Project pr 
left join Cases c on pr.name = c.id
left join ParamValue pv on c.id = pv.parameterId
left join Parameter p on  p.id = pv.parameterId
where p.name = 'turbine' and pv.value = '03';

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

Instead of several nested IN clause with subquery seems more easy to read a proper set of inner join 

select distinct Project.name 
from Project 
INNER JOIN  Cases ON Cases.projectId = Project.id
INNER JOIN ParamValue ON ParamValue.caseId = Cases.id
  AND ParamValue.value ='03'
INNER JOIN Parameter ON ParamValue.parameterId = Parameter.id 
  AND Parameter.name =  'turbine'

Upvotes: 3

Nae
Nae

Reputation: 15335

Sure here you go without subqueries:

SELECT pj.Name
FROM Parameter p
INNER JOIN ParamValue pv ON pv.Value = '03' AND p.Id = pv.parameterId
INNER JOIN Cases c ON pv.caseId = c.Id
INNER JOIN Project pj ON c.projectId = pj.Id
WHERE p.name = 'turbine'
;

Upvotes: 1

Related Questions