SQL (Oracle) nested select parameters

Question

I'm trying to do something like:

select fred.name
  , fred.emp_num
from fred
  , joe
  , (select * from
      (select yr_bonus
        , 1 grade
      from mark 
      where mark.department = fred.department)
      union
      (select
        0 yr_bonus
        , 2 grade
      from dual)
    ) pay
where fred.division = joe.division
and fred.grade = pay.grade;

This doesn't work. I can't seem to figure out how to tie mark.department to fred.department from 2 levels deep in selects. How do I accomplish this?

Mikhail · Accepted Answer · 2013-06-14 13:52:05Z

2

Because in the FROM clause you can't access another table, you must use either JOINS or join tables in where clause. Besides, your query doesn't seem right, describe what are you trying to do?

If I understood correctly, your query can be rewritten like this:

select fred.name
  , fred.emp_num
from fred
  , joe
where fred.division = joe.division
and ((fred.grade = 1 and fred.department in (select department from mark) 
or (fred.grade = 2))

answered Jun 14, 2013 at 13:52

Mikhail

1,5702 gold badges13 silver badges13 bronze badges

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

1 Comment

Travis Over a year ago

This makes sense. What I'm actually trying to do involves significantly more complex queries, in which I have to integrate a few existing queries as subqueries of a new one. This answers my question though - you can't, use joins.

the_slk · Accepted Answer · 2013-06-14 14:24:09Z

1

SELECT  fred.name
,       fred.emp_num
FROM    fred
JOIN    joe
ON      fred.division = joe.division
JOIN
(
        SELECT  *
        FROM
        (
            SELECT  yr_bonus
            ,       1 grade
            FROM    mark
            UNION
            SELECT  0 yr_bonus
            ,       2 grade
            FROM    dual
        ) mark
        JOIN    fred
        ON      mark.department = fred.department
) pay
ON      fred.grade = pay.grade;

answered Jun 14, 2013 at 14:24

the_slk

2,1921 gold badge11 silver badges10 bronze badges

Collectives™ on Stack Overflow

SQL (Oracle) nested select parameters

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related