How to fix SQL query to Left Join a subquery with Where clause?-CodePudding

I'm new to SQL and I'm not certain why I am getting this error. I am trying to left join a sub-query to another query in sql developer.

This is the first query,

SELECT DISTINCT 
   tl.species,
   ag.age
FROM 
   age_list ag,
   tree_list tl
WHERE
  ag.tree_id = tl.tree_id

And then the sub-query I would like to left join where the tree_id = tree_number is,

SELECT DISTINCT 
   sl.tree_spon,
   sl.tree_number
FROM spon_list sl
WHERE
   sl.tree_spon < 10

When trying to do this I've tried to use,

SELECT DISTINCT 
   tl.species,
   ag.age,
   q1.tree_spon
FROM 
   age_list ag,
   tree_list tl

LEFT OUTER JOIN (SELECT DISTINCT 
   sl.tree_spon,
   sl.tree_number
FROM spon_list sl
WHERE sl.tree_spon < 10) q1 on q1.tree_number = tree_list.tree_id

WHERE
  ag.tree_id = tl.tree_id

Whatever I change in terms of the alias' for the columns and tables I always get the error, "ORA-00904: invalid identifier error", and that "tree_list.tree_id is invalid identifier", though separately the queries run fine.

Can anyone help, is it an issue with both queries joining on the tl.tree_id?

CodePudding user response：

You can use the ANSI join syntax throughout (rather than mixing in legacy comma joins), joining on ag.tree_id = sl.tree_number (or tl.tree_id = sl.tree_number but they're both equal given the previous join) and putting the filter on sl.tree_spon < 10 into the ON clause as well:

SELECT DISTINCT 
       tl.species,
       ag.age,
       sl.tree_spon,
       sl.tree_number
FROM   age_list ag
       INNER JOIN tree_list tl
       ON (ag.tree_id = tl.tree_id)
       LEFT OUTER JOIN spon_list sl
       ON (ag.tree_id = sl.tree_number AND sl.tree_spon < 10)

CodePudding user response：

Change tree_list.tree_id to tl.tree_id