CODEWITHSUNDEEP
sqloracle-databaseselection
nitin reddy
nitin reddy

Reputation: 1

Selecting few columns as table

I have a problem in designing a query:

  1. I have to select few records based on criteria

    (SELECT COL_1, COL_2,COL_3 FROM TABLE_1 WHERE COL_3 = 'CND')

  2. Now I need to select records from two databases based on these results

    (SELECT XX_1, XX_2 
 FROM TABLE_2 WHERE TABLE_2.XX1 = TABLE_1.COL1 
(from filtered results in step 1)

  3. (SELECT YY_1, YY_2, YY_3 
 FROM TABLE_3 WHERE TABLE_3.YY_2 = TABLE_1.COL2)
(from filtered results in step 1)

  4. I need results in single table view

    XX_1, XX_2, YY_1, YY_2, YY_3

mentioned columns must be equal to be in result and only record with such equality should be fetched.

I need to run this on millions of records, so performance is considered

It's gonna be used in Java classes, so please don't suggest me any db specific/sql commands which can't be executed since I don't hold any db permissions other than read.

Hope I am clear. In case not, I will explain the doubts.

I tried something like this

SELECT *
FROM TABLE_2
JOIN
(SELECT COL_1, 
    COL_2, 
    COL_3 
 FROM TABLE_1
 WHERE COL_3 = 'CND'
 GROUP BY COL_1) TMP_TABLE
 ON (TMP_TABLE.COL_1 = TABLE2.XX_1)

But I got view/table doesn't exists - oracle error.

Upvotes: 0

Views: 587

Answers (2)

Subek Shakya
Subek Shakya

Reputation: 605

with usedrows as
( select a.Col_1,a.Col_2 FROM table1 a left JOIN table2 b ON a.Col_1=b.Col_2)
select Col_1,C0l_2 from usedrows

This is just an example where usedrows is a virtual table made after join.and u can select the columns from that join table as u select from other table.

Upvotes: 0

joni_demon
joni_demon

Reputation: 666

I think you need to use a subquery,just like this

select col_1,col_2

from(

  select col_1,col_2

  from (

     select col_1,col_2 from table_1

  )tbl1

  left join table_2 tbl2 on tbl2.col_1 = tbl1.col_1

)tbl3

left join table_3 tbl3 on tbl4.col_1 = tbl3.col_1

Upvotes: 1

Related Questions