SQL query from extracted column-CodePudding

I have the following oracle sql query:

SELECT DISTINCT A, B, C, D
FROM my_table
WHERE (B IN (SELECT ID_X FROM table2))

This is fine because B is in table2.

Now I would like to query for column D which is not in table2 but can be mapped from my_table via D.

Table "my_table":

A	B	C	D
abc	def	ghi	jmk

Table "table2":

B	value
def	2

How can I first extract the value of column B if I only have the value of column D:

jmk -> def

And then use that value to extract my data from table2:

def -> 2

CodePudding user response：

Use an inner join:

SELECT DISTINCT t1.*, t2.value
FROM my_table t1
INNER JOIN table2 t2
    ON t2.ID_X = t1.B;

CodePudding user response：

With your sample data:

WITH
    tbl       AS    (   Select 'abc' "A", 'def' "B", 'ghi' "C", 'jmk' "D" From Dual ),
    tbl_1   AS  (   Select 'def' "B", 2 "VALUE" From Dual )

try this:

SELECT      t.A, t.B, t.C, t.D, t1.VALUE "VALUE_OF_B"
FROM        tbl t
LEFT JOIN   tbl_1 t1 ON(t1.B = t.B)
WHERE       t.D = 'jmk'

R e s u l t :
A   B   C   D   VALUE_OF_B
--- --- --- --- ----------
abc def ghi jmk          2

If there is no match of column B in tbl_1 you will get the row where VALUE_OF_B is null. If you put INNER instead of LEFT JOIN then, without the match, no rows will be selected. It's up to you to decide what kind of join to use...