CODEWITHSUNDEEP
sqloracle-databasejoin
Vikram
Vikram

Reputation: 2072

How to remove repeated lines in an Oracle SQL query for multiple parent and child tables

Yesterday I posted a question regarding oracle sql query being repeated.

How to remove repeated lines in an Oracle SQL query

How do I modify the SQL Query with JOIN if I have multiple parent and child tables?

SELECT t1.table_id FROM TABLE_ONE t1, COMMON_TABLE cmn
  WHERE
        t1.table_name = 'TEST_ONE'
    AND t1.table_id = cmn.common_id
    AND cmn.column_one > 20
    AND cmn.column_two <= 30
UNION
  SELECT t2.table_id FROM TABLE_TWO t2, COMMON_TABLE cmn
  WHERE
        t2.table_name = 'TEST_TWO'
    AND t2.table_id = cmn.common_id
    AND cmn.column_one > 20
    AND cmn.column_two <= 30
UNION
  SELECT t3.table_id FROM TABLE_THREE t3, COMMON_TABLE cmn
  WHERE
        t3.table_name = 'TEST_THREE'
    AND t3.table_id = cmn.common_id
    AND cmn.column_one > 20
    AND cmn.column_two <= 30
UNION
  SELECT tc1.table_id FROM TABLE_CHILD_ONE tc1, TABLE_ONE t1, COMMON_TABLE cmn
  WHERE
        tc1.table_name = 'TEST_CHILD_ONE'
    AND tc1.table_id = t1.table_id
    AND cmn.column_one > 20
    AND cmn.column_two <= 30
UNION
  SELECT tc2.table_id FROM TABLE_CHILD_TWO tc2, TABLE_TWO t2, COMMON_TABLE cmn
  WHERE
        tc2.table_name = 'TEST_CHILD_TWO'
    AND tc2.table_id = t2.table_id
    AND cmn.column_one > 20
    AND cmn.column_two <= 30

I would like to remove the repeated lines 

AND cmn.column_one > 20
AND cmn.column_two <= 30

Upvotes: 0

Views: 85

Answers (3)

Abecee
Abecee

Reputation: 2393

You should use UNION ALL unless you really need the data consolidation, which UNION does provide - and which comes at a performance penalty.

(Adjusted the join conditions - no longer three times AND t3.table_id = cmn.common_id).

Just drop the ALL where you need it due to your data's intricacies:

SELECT table_id
FROM (
  (SELECT
    t1.table_id
  FROM TABLE_ONE t1
  WHERE t1.table_name = 'TEST_ONE'
  UNION ALL
  SELECT tc1.table_id
  FROM TABLE_CHILD_ONE tc1
  JOIN TABLE_ONE t1
    ON tc1.table_id = t1.table_id
  WHERE tc1.table_name = 'TEST_CHILD_ONE'
  )
  UNION ALL
  (SELECT t2.table_id
  FROM TABLE_TWO t2
  WHERE t2.table_name = 'TEST_TWO'
  UNION ALL
  SELECT tc2.table_id
  FROM TABLE_CHILD_TWO tc2
  JOIN TABLE_TWO t2
    ON tc2.table_id = t2.table_id
  WHERE tc2.table_name = 'TEST_CHILD_TWO'
  )
UNION ALL
SELECT t3.table_id
FROM TABLE_THREE t3
WHERE t3.table_name = 'TEST_THREE') T
JOIN COMMON_TABLE cmn
  ON T.table_id = cmn.common_id
WHERE cmn.column_one > 20
AND cmn.column_two <= 30;

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Did you try this?

select table_id
from 
(
select table_id, cmn.column_one, cmn.column_two
etc
) derived_table
where column_one > 20
and column_two <= 30

Upvotes: 0

radar
radar

Reputation: 13425

USE UNION and get the values from all tables and then do one JOIN with COMMON_TABLE

SELECT table_id 
FROM 
(
   SELECT t1.table_id FROM TABLE_ONE t1 
   WHERE  t1.table_name = 'TEST_ONE'
   UNION 
   SELECT t2.table_id FROM TABLE_TWO t2
   WHERE  t2.table_name = 'TEST_TWO'
   UNION
   SELECT tc1.table_id FROM TABLE_CHILD_ONE tc1
   JOIN TABLE_ONE t1
   ON tc1.table_name = 'TEST_CHILD_ONE'
   AND tc1.table_id = t1.table_id
) T
JOIN COMMON_TABLE cmn
ON T.table_id = cmn.common_id
AND cmn.column_one > 20
AND cmn.column_two <= 30

Upvotes: 3

Related Questions