CODEWITHSUNDEEP
sqloracle-databasesubqueryleft-join
pjb216
pjb216

Reputation: 13

SQL Left Join to Sub-select

I'm trying to do a query against 3 tables. I'm looking for duplicates on the first two tables, and I have that part working ok:

select b.stn_cd, b.dept_cd, a.emp_nbr, b.name, a.vacn_bid_prd
from tableA a, tableB b
where a.emp_nbr = b.emp_nbr
and a.vacn_bid_prd = 34
group by a.emp_nbr, a.vacn_bid_prd, b.stn_cd, b.dept_cd, b.name
having count(*) > 1
order by b.stn_cd, b.dept_cd, b.name;

Next, I want to add a column from a third table. Since I don't want to include it in the 'group by' I thought I should do a left join, such as:

select b.stn_cd, b.dept_cd, a.emp_nbr, b.name, a.vacn_bid_prd
from tableA a, tableB b
LEFT JOIN
    (
        select emp_nbr, default_work_area
        from tableC c
    ) as c
on a.emp_nbr = c.emp_nbr
where a.emp_nbr = b.emp_nbr
and a.vacn_bid_prd = 34
group by a.emp_nbr, a.vacn_bid_prd, b.stn_cd, b.dept_cd, b.name
having count(*) > 1
order by b.stn_cd, b.dept_cd, b.name;

I'm getting this error: Error: ORA-00905: missing keyword SQLState: 42000 ErrorCode: 905 Position: 176

I'm fairly new to sql, but I followed a few examples I found on this site and can't figure out why this isn't working, specifically this one SQL LEFT JOIN Subquery Alias. My first post here, I think I have it formatted right but apologize if not. Thanks.

Upvotes: 1

Views: 2387

Answers (1)

radar
radar

Reputation: 13425

use explicit join syntax

you need to use a subquery for this

select T.stn_cd, T.dept_cd, T.emp_nbr, T.name, T.vacn_bid_prd, c.default_work_area
from
(
   select b.stn_cd, b.dept_cd, a.emp_nbr, b.name, a.vacn_bid_prd
   from tableA a
   join tableB b
   on a.emp_nbr = b.emp_nbr
   and a.vacn_bid_prd = 34
   group by a.emp_nbr, a.vacn_bid_prd, b.stn_cd, b.dept_cd, b.name
   having count(*) > 1

) T
left join tableC c
on T.emp_nbr = c.emp_nbr

Upvotes: 1

Related Questions