Home » SQL & PL/SQL » SQL & PL/SQL » Table join with distinct records (oracle 9i)
Table join with distinct records [message #377492] Tue, 23 December 2008 04:24 Go to next message
Supertd
Messages: 15
Registered: December 2008
Location: Africa
Junior Member
Good day,

I need to create a query to display 4 columns(from different tables) and need the first column NOT to have repeats.
The query that i have written is giving me repeat records in the first column even though the records in that table are unique. My query is below
select distinct vs.policy_no, bes.entity_no, bec.eff_from, vs.level_cd from
Vitality_status vs
left join benefit_entities bes on vs.policy_no = bes.policy_no
left join benefit_entity_compos bec on bes.benefit_entity_id=bec.benefit_entity_id
left join policy_trns pt on vs.policy_no = pt.policy_no
where
     ((sysdate between vs.eff_from and vs.eff_to) and (vs.level_cd>2))
 and
     ((bec.eff_from < to_date(20080101,'YYYYMMDD')) and (bec.compo_id='VIRACT'))
 and
     ((pt.prod_cd='VITA') and (pt.internal_co=11) and (pt.eff_date=to_date('20090101','YYYYMMDD')))



Could someone please advise on what I am doing wrong. I dont seem to be able to figure it out. Any help would be greatly appreciated.
Thanks in advance and have a great day.
Re: Table join with distinct records [message #377495 is a reply to message #377492] Tue, 23 December 2008 04:33 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
simple union will avoid duplicates
yours
dr.s.raghunathan
Re: Table join with distinct records [message #377500 is a reply to message #377492] Tue, 23 December 2008 04:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
DISTINCT works on the entire column set returned - it ensures that each set ov values only appears once in the output list.

If you had an ORDER BY clause on yuor query, I'd assume you wre producing some sort of report and wanted each Policy_No to appear once with all the related records underneath it.
As there is no ORDER BY clause, there is no guarantee that all the rows for one Policy_no will appear in order, and so only displayin each policy_no once would make no sense.

Can you tell us what you're trying to achieve here?
Re: Table join with distinct records [message #377501 is a reply to message #377495] Tue, 23 December 2008 04:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A DISTINCT will remove duplicates in exactly the same way that a UNION will - neither of them will work on a single column.
Re: Table join with distinct records [message #377510 is a reply to message #377500] Tue, 23 December 2008 05:12 Go to previous messageGo to next message
Supertd
Messages: 15
Registered: December 2008
Location: Africa
Junior Member
The table from which I am retieving (or at least want to retrieve) the policy number, currently contains unique records. I want to display this policy number and then match up the rest of the colums to this. My problem here is people are being charged incorrectly (people that have multiple branches under a single policy number - eg family policies) for stuff and I just want to see the number of policies that are affected, but also want to display the other columns just to confirm that the 'where' clauses are being implemented correctly.
I hope this makes sense. I have also tried the previous code with 'ORDER BY vs.policy_number' at the end but I still get duplicates on the first column. I have attached a screenshot of sample output.
Thanks for your help.
Re: Table join with distinct records [message #377516 is a reply to message #377510] Tue, 23 December 2008 05:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do a MAX() or MIN() on the columns that are from tables contributing to the duplicates.

Or better yet, join only on unique keys; or add fiters to the joins so that only one row matches.

Ross Leishman
Re: Table join with distinct records [message #377522 is a reply to message #377492] Tue, 23 December 2008 05:34 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
You are aware, that "display the other columns" leads to multiple rows, as there are multiple matching rows in other tables. As you do not want to see the details, use aggregation (GROUP BY) on the first table columns. To "see the number of policies that are affected", use COUNT function.

If you want to see all values of the other table columns, have a look at this AskTom page: String Concatenation. The function is not built-in, you shall create it on your system.
Re: Table join with distinct records [message #377531 is a reply to message #377492] Tue, 23 December 2008 06:30 Go to previous messageGo to next message
Supertd
Messages: 15
Registered: December 2008
Location: Africa
Junior Member
Hi again,

I have now tried the code below and it seems to return distinct records. is this query possibly correct? please assist.
select distinct vs.policy_no, MAX(bes.entity_no), MAX(bec.eff_from), MAX(vs.level_cd) from
Vitality_status vs
left join benefit_entities bes on vs.policy_no = bes.policy_no
left join benefit_entity_compos bec on bes.benefit_entity_id=bec.benefit_entity_id
left join policy_trns pt on vs.policy_no = pt.policy_no
where
     ((sysdate between vs.eff_from and vs.eff_to) and (vs.level_cd>2))
 and
     ((bec.eff_from < to_date(20080101,'YYYYMMDD')) and (bec.compo_id='VIRACT'))
 and
     ((pt.prod_cd='VITA') and (pt.internal_co=11) and (pt.eff_date=to_date('20090101','YYYYMMDD')))
     group by vs.policy_no
Re: Table join with distinct records [message #377558 is a reply to message #377492] Tue, 23 December 2008 08:35 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't need a distinct if you're using group by.
Group by gives distinct values for the grouped columns by definition.
Previous Topic: calling procedure inside a procedure
Next Topic: Oracle Update Trigger that will Not fire on certain fields (merged)
Goto Forum:
  


Current Time: Thu Dec 08 15:58:12 CST 2016

Total time taken to generate the page: 0.14713 seconds