combine multiple select statement in SQL

Question

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

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')
    )
;

It's better to join the tables than to do a bunch of subqueries like that — babno
– babno, Commented Feb 20, 2020 at 16:47

ScaisEdge · Accepted Answer · 2020-02-21 09:37:32Z

3

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'

edited Feb 21, 2020 at 9:37

answered Feb 20, 2020 at 16:51

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Haribo Over a year ago

I'm getting the error : Msg 209, Level 16, State 1, Line 18 Ambiguous column name 'name'

Werner Henze Over a year ago

@user9112767 Did you try changing name to Project.name (or replace Project. with the table where name comes from).

Haribo Over a year ago

@WernerHenze, Yes, I have put the Project. before the name, but the result here is 114 rows and my select statement returns the 11 rows !

ScaisEdge Over a year ago

@user9112767 answer updated .. with proper table name and distinct for avoid duplication

ScaisEdge Over a year ago

@user9112767 the distinct clause should avoid rows duplications ...

|

Nae · Accepted Answer · 2020-02-20 16:48:49Z

1

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'
;

answered Feb 20, 2020 at 16:48

Nae

15.5k8 gold badges62 silver badges89 bronze badges

Comments

Noureddine · Accepted Answer · 2020-02-21 17:45:33Z

1

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';

edited Feb 21, 2020 at 17:45

answered Feb 20, 2020 at 16:59

Noureddine

1031 gold badge6 silver badges18 bronze badges

2 Comments

Haribo Over a year ago

@WernerHenze, the same error with your query : Msg 209, Level 16, State 1, Line 18 Ambiguous column name 'name'

Werner Henze Over a year ago

@user9112767 Not my query. I was just editing the question and fixed a type (Projetc -> Project). The answer is from Noureddine.

Collectives™ on Stack Overflow

combine multiple select statement in SQL

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related