CODEWITHSUNDEEP
sqloracle-database
Vikram
Vikram

Reputation: 2072

How to remove repeated lines in an Oracle SQL query

I have a Query 

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_ONE 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_ONE 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 t3.table_id = cmn.common_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

and put it at the end. Can you kindly help me out? The problem of using joins as mentioned in answers is, I m not sure as how to put joins with child tables too. I had the option of using nested select. But was trying to use joins for better performance.

Upvotes: 1

Views: 132

Answers (2)

Deep
Deep

Reputation: 3202

You can rewrite this query with join syntax like : 

SELECT DISTINCT t.table_id
FROM   TABLE_ONE t
       JOIN COMMON_TABLE cmn
         ON t.table_id = cmn.common_id
WHERE  t.table_name IN( 'TEST_ONE', 'TEST_TWO', 'TEST_THREE' )
       AND cmn.column_one > 20
       AND cmn.column_two <= 30

DISTINCT will remove duplicate records.

EDIT :

you can include your child table like :

SELECT DISTINCT t.table_id
FROM   TABLE_ONE t
       JOIN COMMON_TABLE cmn
         ON t.table_id = cmn.common_id
       JOIN TABLE_CHILD_ONE tc
         ON t.table_id = tc.table_id
WHERE  ( tc.table_name IN( 'TEST_CHILD_ONE', 'TEST_CHILD_TWO', 'TEST_CHILD_THREE' )
          OR t.table_name IN( 'TEST_ONE', 'TEST_TWO', 'TEST_THREE' ) )
       AND cmn.column_one > 20
       AND cmn.column_two <= 30

Upvotes: 3

Babu James
Babu James

Reputation: 2843

you can remove common limit condition like below 

 SELECT table_id FROM 
    (SELECT t1.table_id, cmn.column_one, cmn.column_two  FROM TABLE_ONE t1, COMMON_TABLE cmn
      WHERE
            t1.table_name = 'TEST_ONE'
    UNION
      SELECT t2.table_id, cmn.column_one, cmn.column_two  FROM TABLE_ONE t2, COMMON_TABLE cmn
      WHERE
            t2.table_name = 'TEST_TWO'
    UNION
      SELECT t3.table_id, cmn.column_one, cmn.column_two  FROM TABLE_ONE t3, COMMON_TABLE cmn
      WHERE
            t3.table_name = 'TEST_THREE') AS MyTable
            WHERE
        column_one > 20
        column_two <= 30

Upvotes: 1

Related Questions