CODEWITHSUNDEEP
sqloracle-database
dang
dang

Reputation: 2412

Select another column of matching record - Oracle SQL

I have this query:

SELECT
    t1.*,
            (
                SELECT
                    MIN(t2.e_nm)
                FROM
                    table2 t2
                WHERE
                    t2.c_type = t1.c_type
                    AND t2.h_level = t1.h_level
                    AND t2.loop = t1.loop
                    AND t2.e_id = t1.e_id
                HAVING
                    COUNT(*) = 1
            ) AS e_nm
        FROM
            table1 t1
        ORDER BY
            t1.f_name,
            t1.line_num;

When e_nm gets selected from table2 as second parameter, I also want to grab another column of matching record - seq_nm from table1.

How can I do it in the above query?

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If the count(*) = 1, then you can use a join with aggregation

SELECT t1.*, t2.e_nm, t2.x
FROM table1 t1 LEFT JOIN
     (SELECT t2.c_type, t2.h_level, t2.loop, t2.e_id,
             MIN(t2.e_nm) as e_nm, MIN(x) as x
      FROM table2 t2
      GROUP BY t2.c_type, t2.h_level, t2.loop, t2.e_id
      HAVING COUNT(*) = 1
     ) t2
     ON t2.c_type = t1.c_type AND
        t2.h_level = t1.h_level AND
        t2.loop = t1.loop AND
        t2.e_id = t1.e_id
ORDER BY t1.f_name, t1.line_num;

This works because you have the COUNT(*) = 1, so only one row matches. If you didn't, you could still use KEEP:

MIN(x) KEEP (DENSE_RANK FIRST ORDER B t2.e_num ASC) as x

Upvotes: 1

Related Questions