Error
Error

Reputation: 825

How select from query inside case in select

I have following query

SELECT DISTINCT v.id, 
                ( CASE 
                    WHEN date IS NULL THEN v.o_id 
                    ELSE (SELECT os 
                          FROM   (SELECT o_id1 os 
                                  FROM   tablet t2 
                                  WHERE  t2.vso_id = v.id 
                                  ORDER  BY date ASC) 
                          WHERE  rownum = 1) 
                  END ) AS p_o, 
                v.o_id  AS k_o 
FROM   tablev v 
       LEFT JOIN tablet t 
              ON v.id = t.v_id; 

Here is what I need: TableV has distinct v.id values. I need join that table with another (tableT) which has many records for same v.id and then for each distinct v.id:

  1. select O_ID from V
  2. if record for v.id in tableT does not exists(date is null) then select O_ID from V (same as above)else select o_id1 from T with minimum date

Also, aditional, is if possible to join o_id from 1. and o_id/o_id1 from2 to another table and write person's name? I would like, instead of o_id, for each v.id to join selected o_id to another table(tableO) and select name.

So, I need these columns in result: V.id, p_o, k_o But it would be really great if it looks like: v.id, p_o.name, k_o.name

Upvotes: 1

Views: 102

Answers (1)

Ishamael
Ishamael

Reputation: 12795

To answer your first question, assuming that t.vso_id and t.v_id are the same thing (otherwise I might have misunderstood your question), try this query (I use another DBMS, so you might need to adjust the call to COALESCE a little):

SELECT v.id, COALESCE(t.o_id1, v.o_id), v.o_id
FROM tableV v 
     LEFT JOIN (
         (SELECT vso_id, min(date) AS min_date FROM tableT GROUP BY vso_id) min_dates
         JOIN tableT t
         ON min_dates.vso_id = t.vso_id AND min_dates.min_date = t.date
     ) ON v.id = t.v_id;

Here's a breakdown:

(SELECT vso_id, min(date) AS min_date FROM tableT GROUP BY vso_id) AS min_dates

selects a smallest date for each vso_id.

 (SELECT vso_id, min(date) AS min_date FROM tableT GROUP BY vso_id) AS min_dates
 JOIN tableT t
 ON min_dates.vso_id = t.vso_id AND min_dates.min_date = t.date

selects one row per vso_id, and that row will correspond to the smallest date that that vso_id has. Finally, I join tableV to that query. COALESCE(t.o_id1, v.o_id) will return t.o_id if it is not NULL, v.o_id otherwise. You can use CASE instead, but I find COALESCE to be a better fit for this particular purpose.

To answer your second question, you can then JOIN against the table with names like this:

JOIN table_with_names twn1 ON twn1.id = COALESCE(t.o_id1, v.o_id)
JOIN table_with_names twn2 ON twn2.id = v.o_id

and project twn1.name and twn2.name.

Upvotes: 1

Related Questions