Reputation: 2072
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
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
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