Join with 'conditioned subquery'

Question

I would like to join a 'conditioned subquery'. Let's imagine that we have a table with certain data containing an ID, a created TS and some or one value:

data_table (ID, created_ts, value) .... "652351, 2018-04-03 06:11:31.996, 1" and so on.

      SELECT dt1.ID
           , dt1.created_ts
           , (SELECT value FROM data_table dt2 WHERE dt1.ID = dt2.ID ) AS value
        FROM data_table dt1

This query would recreate the table. We assume that the IDs are unique. The ID of the outer query is determining the data set of the query for the attribute value.

Now I would like to write the same example if a different form. As a join of two queries.

     SELECT dt1.ID
          , dt1.created_ts
          , dt2.value
      FROM data_table dt1 
 LEFT JOIN ( SELECT ID, value FROM data_table WHERE dt1.ID = data_table.ID
         ) dt2

As I understand it this query should return the same data or result but it is not working. It throws an error:

SQL-Error [904] [42000]: ORA-00904: "dt1"."ID": invalid ID

What is wrong with the second SQL query?

I know that a query like this

 SELECT dt1.ID
      , dt1.created_ts
      , dt2.value
   FROM data_table dt1 LEFT JOIN (SELECT value FROM data_table) dt2
  WHERE dt1.ID = dt2.ID

would work but this form will not deliver the same data in a more complex example. Thanks

A woriking example:

WITH data_table AS ( select '101'                               AS ID
                          , to_date('2022-01-01', 'YYYY-MM-DD') AS Created_Date
                          , 3                                   AS Value
                       from dual 
                      UNION ALL
                     select '102', to_date('2022-02-01', 'YYYY-MM-DD'), 7  from dual
                      UNION ALL
                     select '103', to_date('2022-03-01', 'YYYY-MM-DD'), 15 from dual
)
   SELECT dt1.ID
        , dt1.Created_Date
        , (Select dt2.value FROM data_table dt2 WHERE dt2.ID = dt1.ID)   AS dt2_Value
        , dt3.Value                                                      AS dt3_Value
     FROM data_table                               dt1 
LEFT JOIN ( Select ID, value FROM data_table )     dt3 
       ON dt3.ID = dt1.ID;

A working example with ununique IDs:

WITH data_table AS ( select '101'                               AS ID
                          , to_date('2022-01-01', 'YYYY-MM-DD') AS Created_Date
                          , 3                                   AS Value
                       from dual 
                    UNION ALL
                     select '102', to_date('2022-02-01', 'YYYY-MM-DD'), 7  from dual
                    UNION ALL
                     select '103', to_date('2022-03-01', 'YYYY-MM-DD'), 15 from dual 
                    UNION ALL
                     select '101', to_date('2022-01-02', 'YYYY-MM-DD'), 30 from dual
                    UNION ALL
                     select '102', to_date('2022-02-02', 'YYYY-MM-DD'), 90 from dual
)
   SELECT dt1.ID
        , dt1.Created_Date
        , dt1.Value
        , ( Select dt2.value 
              From data_table dt2 
             Where dt2.ID = dt1.ID
             Order By dt2.Created_Date DESC Fetch First 1 Rows Only )      AS dt2_Value
        , dt3.Value                                                        AS dt3_Value
        , dt4.Value                                                        AS dt4_Value
     FROM data_table dt1 
       --
LEFT JOIN ( Select ID, Value 
              From data_table 
             Order By data_table.Created_Date DESC Fetch First 1 Rows Only 
        ) dt3 
       ON dt3.ID = dt1.ID
       --
LEFT JOIN ( Select ID, Value 
              From data_table 
             WHERE Value < 15 
             Order By data_table.Created_Date DESC Fetch First 1 Rows Only 
        ) dt4 
       ON dt4.ID = dt1.ID
       ;

DT3 will join first record of data_table with the identical IDs. And you can also use conditions to further reduce your records of the subquery. But when you want to use data of the table dt1 to reduce the data of the subquery it will fail with the error message.

WITH data_table AS ( select '101'                               AS ID
                          , to_date('2022-01-01', 'YYYY-MM-DD') AS Created_Date
                          , 3                                   AS Value
                       from dual 
                    UNION ALL
                     select '102', to_date('2022-02-01', 'YYYY-MM-DD'), 7  from dual
                    UNION ALL
                     select '103', to_date('2022-03-01', 'YYYY-MM-DD'), 15 from dual 
                    UNION ALL
                     select '101', to_date('2022-01-02', 'YYYY-MM-DD'), 30 from dual
                    UNION ALL
                     select '102', to_date('2022-02-02', 'YYYY-MM-DD'), 90 from dual
)
   SELECT dt1.ID
        , dt1.Created_Date
        , dt1.Value
        , ( Select dt2.value 
              From data_table dt2 
             Where dt2.ID = dt1.ID
          Order By dt2.Created_Date DESC Fetch First 1 Rows Only )      AS dt2_Value
        , dt5.Value                                                        AS dt5_Value
     FROM data_table dt1 
       --
LEFT JOIN ( Select ID, Value 
              From data_table 
             WHERE data_table.Value = dt1.Value 
          Order By data_table.Created_Date DESC Fetch First 1 Rows Only 
        ) dt5 
       ON dt5.ID = dt1.ID
       ;

Does it work if you replace `left join` with `outer apply`? (requires Oracle 12.1 or later) — William Robertson, Apr 15 '22 at 22:24
You give invalid code but don't give the rule for how you expect its invalid part to be interpreted. The right of a JOIN takes a table & alias. How does the right hand subquery denote a table? In standard SQL LATERAL could appropriately be added to this query to get a valid one, but it would mean what the standard says it means, which might or might not be what you wish. PS This is a faq, but to (re)search you need to google many clear generic phrasings of your situation/problem/goal/question, and there's none here. [mre] [ask] [Help] PS Quotes don't clarify unclear phrases. — philipxy, Apr 16 '22 at 01:59
You are right. And LATERAL is everything I need to add to my query. Thank you. — Air Zrakovic, Apr 16 '22 at 11:14
See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify one non-sole non-poster commenter `x` re a comment. Posters, sole commenters & followers of posts always get notified. — philipxy, Apr 16 '22 at 11:55

score 2 · Answer 1 · answered Apr 15 '22 at 16:07

2

I don't really understand your query, but does this code do what you want it to do?

SELECT dt1.ID, dt1.created_ts, dt2.value
FROM data_table dt1 LEFT JOIN data_table dt2
ON dt1.ID = dt2.ID

db-fiddle

answered Apr 15 '22 at 16:07

Prosto_Oleg

322
3
13

Yes, this is the way of joining the table with itself when `ID` is unique. We don't need a `LEFT JOIN`; an inner join (`JOIN`) would be appropriate. – The Impaler Apr 15 '22 at 16:18
@Prosto_Oleg i see your queries. The second one give an syntax error. You can solve it very quickly ,just replace left join with cross apply. – Florin Apr 15 '22 at 21:45
@Florin. A query with a syntax error is an example from the question – Prosto_Oleg Apr 16 '22 at 09:03
That is the common way to join the table with itself. It is similay to my 3ed query. But I really need to filter the subtable or to build a spacific subset before joining it to dt1. – Air Zrakovic Apr 16 '22 at 10:19

score 0 · Answer 2 · answered Apr 16 '22 at 10:54

Great!! outer apply will do it. Page (LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in Oracle Database 12c Release 1 ) is providing examples and explanations ...

Outer Apply and Cross Join Latera in combination with my example:

WITH data_table AS ( select '101'                               AS ID
                          , to_date('2022-01-01', 'YYYY-MM-DD') AS Created_Date
                          , 3                                   AS Value
                       from dual 
                    UNION ALL
                     select '102', to_date('2022-02-01', 'YYYY-MM-DD'), 7  from dual
                    UNION ALL
                     select '103', to_date('2022-03-01', 'YYYY-MM-DD'), 15 from dual 
                    UNION ALL
                     select '101', to_date('2022-01-02', 'YYYY-MM-DD'), 30 from dual
                    UNION ALL
                     select '102', to_date('2022-02-02', 'YYYY-MM-DD'), 90 from dual
)
   SELECT dt1.ID
        , dt1.Created_Date
        , dt1.Value
        , ( Select dt2.value 
              From data_table dt2 
             Where dt2.ID = dt1.ID
             Order By dt2.Created_Date DESC Fetch First 1 Rows Only )      AS dt2_Value
        , dt5.Value                                                        AS dt5_Value
        , dt6.Value                                                        AS dt6_Value
     FROM data_table dt1 
       --
OUTER apply        ( Select ID, Value 
                       From data_table 
                      WHERE Value = dt1.Value 
                   Order By data_table.Created_Date DESC Fetch First 1 Rows Only 
                   ) dt5
        --           
cross join lateral ( Select ID, Value 
                       From data_table 
                      WHERE Value = dt1.Value 
                   Order By data_table.Created_Date DESC Fetch First 1 Rows Only 
                   ) dt6                   
       ;

@William Robertson and @Florin thanks again.

Join with 'conditioned subquery'

2 Answers2