| Table join with distinct records [message #377492] |
Tue, 23 December 2008 04:24  |
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 #377500 is a reply to message #377492] |
Tue, 23 December 2008 04:53  |
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 #377510 is a reply to message #377500] |
Tue, 23 December 2008 05:12 |
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 |
rleishman
Messages: 3728
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 |
flyboy
Messages: 1903
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
