Home » SQL & PL/SQL » SQL & PL/SQL » Union problem
icon5.gif  Union problem [message #247770] Wed, 27 June 2007 02:57 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
hi

i need the query to fetch the data ...
i have two table
1. table1
2. table2

each table has id uniquely, and each table has rep_id but it is not unique.
i retrieved the data from both table using union. now i need the data for the rep who is in both table. i want to check union by id, and i need to retrieve the reps who is in both tables.

my code is

select "table1"."ID" as "ID",
"table1"."REP_ID" as "REP_ID",
"table1"."REGION" as "REGION",
"table1"."LOB" as "LOB",
"table1"."PLAN_HIERARCHY_CHANGE" as "PLAN_HIERARCHY_CHANGE"

from "table1" "table1" ,"table2" "table2" where "table1"."REP_ID" IN (SELECT REP_ID FROM table2)

UNION

SELECT "table2"."ID" as "ID",
"table2"."REP_ID" as "REP_ID",
"table2"."REGION" as "REGION",
"table2"."LOB" as "LOB",
"table2"."PLAN_HIERARCHY_CHANGE" as "PLAN_HIERARCHY_CHANGE"

from "table2" "table2"


THIS QUERY GIVES ALL UNION RESULT, BUT IT DOES'T FILTER THE REP_ID, IT SHOWS ALL THE REP_ID, I NEED THE REPS WHO IS IN BOTH TABLES

CAN ANYONE PLEASE TELL ME THE SOLUTION
Re: Union problem [message #247773 is a reply to message #247770] Wed, 27 June 2007 03:09 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
SELECT table1.ID AS ID
     , table1.rep_id AS rep_id
     , table1.region AS region
     , table1.LOB AS LOB
     , table1.plan_hierarchy_change AS plan_hierarchy_change
FROM   table1 table1
     , table2 table2
WHERE  table1.rep_id = table2.rep_id
UNION
SELECT table2.ID AS ID
     , table2.rep_id AS rep_id
     , table2.region AS region
     , table2.LOB AS LOB
     , table2.plan_hierarchy_change AS plan_hierarchy_change
FROM   table1 table1
     , table2 table2
WHERE  table1.rep_id = table2.rep_id


MHE
Previous Topic: problem in dynamic sql
Next Topic: get last inserted record
Goto Forum:
  


Current Time: Tue Dec 06 00:15:48 CST 2016

Total time taken to generate the page: 0.30501 seconds