All Forums Database
Jay 1 post Joined 05/08
23 May 2008
Optimize a query containing UNION

Table1: Fields - Reg_no, subject1, subject2, subject3, subject4, subject5, subject6,source.Table2: Fields - Reg_no, subject_nmI need to load data from the above 2 tables into a new table with only 2 fields - Reg_no and subject. I use the following select statement to pull data from table1 and table2.select Reg_no,subject1from table1where subject1 is not nulland source = 'IS';UNIONselect Reg_no,subject2from table1where subject2 is not nulland source = 'IS';UNIONselect Reg_no,subject3from table1where subject3 is not nulland source = 'IS';UNIONselect Reg_no,subject4from table1where subject4 is not nulland source = 'IS';UNIONselect Reg_no,subject5from table1where subject5 is not nulland source = 'IS';UNIONselect Reg_no,subject6from table1where subject6 is not nulland source = 'IS';UNIONselect Reg_no,subject_nmfrom table2;Is there a way to optimize this query?

Adeel Chaudhry 773 posts Joined 04/08
23 May 2008

Hello,Create a VT table (suppose vtTable1) with 1 column and insert all the subject names in it, e.g.:Then use it in cross-join with the select statement as:select a.Reg_no, b.subjectfrom table1 a cross join vtTable1 bwhere a.subject1 is not nulland a.source = 'IS';I haven't tried it so do match the results and let know if they are exactly as you like. :)Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
23 May 2008

Little change:select a.Reg_no, b.subjectfrom table1 a cross join vtTable1 bwhere a.subject1 is not nullAND a.subject2 is not nullAND a.subject3 is not nullAND a.subject4 is not nullAND a.subject5 is not nulland a.source = 'IS';HTHRegards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.