Hi Gurus,
I have three tables. I want to join all tables using union in SQL statement. The query is returning all the records from both tables but i only require unique rows based on a specific column value. Here is my table structure -
TableA -
LIC_ID NUMBER(10) NOT NULL
LIC_NUMBER VARCHAR2(20)
COMMENCE_DATE DATE
EXPIRY_DATE DATE
TERM VARCHAR2(20)
LIC_ID is the primary key in this table -
Sample data from TableA
LIC_ID LIC_NUMBER COMMENCE_DATE EXPIRY_DATE TERM
2 TR4323 12/04/2008 11/03/2010 2 Years
34 TR5432 23/07/2009 22/07/2010 1 Year
45 TR5321 24/06/2009 23/06/2010 1 Year
65 TR6666 23/07/2010 22/07/2011 1 Year
32 TR2423 30/05/2010 29/05/2011 1 Year
TableB -
MAR_ID NUMBER(10) NOT NULL
LIC_ID NUMBER(10) NOT NULL
ZONE_NAME VARCHAR2(20)
DEPARTMENT VARCHAR2(20)
ACTIVITIES VARCHAR2(200)
COMMENTS VARCHAR2(200)
MAR_ID is the primary key in this table and LIC_ID is the foreign key on TableA
Sample data from TableB -
MAR_ID LIC_ID ZONE_NAME DEPARTMENT ACTIVITIES COMMENTS
23 2 ZONE A IT NONE
43 34 ZONE B IT NONE
33 65 ZONE C ACCOUNT NONE
TableC
REC_ID NUMBER(10) NOT NULL
LIC_ID NUMBER(10) NOT NULL
DIST_NAME VARCHAR2(20)
REGION VARCHAR2(20)
ACTIVITIES VARCHAR2(200)
COMMENTS VARCHAR2(200)
REC_ID is the primary key in this table and LIC_ID is the foreign key.
Sample data -
REC_ID LIC_ID DIST_NAME REGION ACTIVITIES COMMENTS
2 45 SA NORTH NONE
3 65 TA NORTH NONE
5 32 NT SOUTH NONE
Here is my sql query -
select a.lic_id, a.lic_number, a.commence_date, a.expiry_date from
TableA a, TableB b
where a.lic_id=b.lic_id
union
select a.lic_id, a.lic_number, a.commence_date, a.expiry_date from
TableA a, TableC c
where a.lic_id=c.lic_id
The above query returns -
lic_id lic_number commence_date expiry_date
2 TR4323 12/04/2008 11/03/2010
34 TR5432 23/07/2009 22/07/2010
45 TR5321 24/06/2009 23/06/2010
65 TR6666 23/07/2010 22/07/2011
32 TR2423 30/05/2010 29/05/2011
65 TR6666 23/07/2010 22/07/2011
LIC_ID 65 exists in both table TableB and TableC hence it repeats in query but I want to display that only once. How can I do that? I want to return unique record on LIC_NUMBER.
Hope this make sence.
Many thanks,
Tajuddin