Remove duplicate records from this SQL Query

I'm performing a query using the Timberline (Simba) ODBC.  My last join started to pull duplicate records.  The lease, or tenant.name field should be unique.  

Can anyone help me make this query remove duplicate records?

select
        lease_pml_lease.lease,tenant_pmn_tenant.name,lease_pml_lease.property,
        lease_pml_lease.primary_unit,lease_pml_lease.status,
        lease_pml_lease_recurring_charge.last_amount,
        tenant_pmn_tenant_address.contact_phone,tenant_pmn_tenant_address.Contact_fax,
        lease_pml_lease.Original_Start,lease_pml_lease.actual_start,
        lease_pml_lease.Original_Lease_end,lease_pml_lease.Actual_lease_end,
        lease_pml_lease.Actual_Movein_date,lease_pml_lease.actual_moveout_date
from lease_pml_lease
inner join tenant_pmn_tenant on  primary_tenant = tenant_pmn_tenant.tenant
inner join TENANT_PMN_TENANT_ADDRESS on primary_tenant = TENANT_PMN_TENANT_ADDRESS.tenant
inner join lease_pml_lease_recurring_charge on  lease_pml_lease.lease = lease_pml_lease_recurring_charge.lease
where status='Current' and lease_pml_lease_recurring_charge.charge_type='Rent'

Thanks,
looknow12Asked:
Who is Participating?
 
randomjamesConnect With a Mentor Commented:
If they are not unique then of course they will result in duplicates so it's important that all fields are unique.

If other fields are not unique then they will need to be handled in a similar way or you can just add grouping to the main query.

This is not optimized SQL and I would expect that it could be streamlined. I was just hoping to convey general principles in my examples.
0
 
randomjamesCommented:
It looks like there is a flaw in the logic if there are multiple records in lease_pml_lease_recurring_charge.

Is that correct? If so, what's the difference in those records?

I ask because I would think that if all the duplicates have the same values for all fields then you could just SELECT DISTINCT,

but if they don't (and you need to describe which fields of your duplicate records are the same and which ones are different) then you may have to group and summarize.
0
 
looknow12Author Commented:
The lease field from the primary table should be unique... lease_pml_lease.lease
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
randomjamesCommented:
So are your duplicates identical on all other fields?
0
 
looknow12Author Commented:
Not always, but they are definitely duplicates in the lease field.  As long as I get unique records based on this lease field I'm happy.
0
 
randomjamesCommented:
I would expect that the field lease_pml_lease_recurring_charge.last_amount needs to be summarized in some way, or you really need to join to the results of a subquery instead of lease_pml_lease_recurring_charge that selects one single last_amount based on the MAX(date) for each lease.

Try this, I think it will give you no duplicates but I doubt it's the actual value for last_amount that you want to use...
select
	lease_pml_lease.lease,
	tenant_pmn_tenant.name,
	lease_pml_lease.property,
	lease_pml_lease.primary_unit,
	lease_pml_lease.status,
	MAX(lease_pml_lease_recurring_charge.last_amount),
	tenant_pmn_tenant_address.contact_phone,
	tenant_pmn_tenant_address.Contact_fax,
	lease_pml_lease.Original_Start,
	lease_pml_lease.actual_start,
	lease_pml_lease.Original_Lease_end,
	lease_pml_lease.Actual_lease_end,
	lease_pml_lease.Actual_Movein_date,
	lease_pml_lease.actual_moveout_date
from 
	lease_pml_lease
	inner join tenant_pmn_tenant on  primary_tenant = tenant_pmn_tenant.tenant
	inner join TENANT_PMN_TENANT_ADDRESS on primary_tenant = TENANT_PMN_TENANT_ADDRESS.tenant
	inner join lease_pml_lease_recurring_charge on  lease_pml_lease.lease = lease_pml_lease_recurring_charge.lease
where 
	status='Current' 
	and lease_pml_lease_recurring_charge.charge_type='Rent'
group by
	lease_pml_lease.lease,
	tenant_pmn_tenant.name,
	lease_pml_lease.property,
	lease_pml_lease.primary_unit,
	lease_pml_lease.status,
	tenant_pmn_tenant_address.contact_phone,
	tenant_pmn_tenant_address.Contact_fax,
	lease_pml_lease.Original_Start,
	lease_pml_lease.actual_start,
	lease_pml_lease.Original_Lease_end,
	lease_pml_lease.Actual_lease_end,
	lease_pml_lease.Actual_Movein_date,
	lease_pml_lease.actual_moveout_date

Open in new window

0
 
randomjamesCommented:
I guess I mean something like this (best guess not knowing your tables or any Simba SQL quirks);
select
	lease_pml_lease.lease,
	tenant_pmn_tenant.name,
	lease_pml_lease.property,
	lease_pml_lease.primary_unit,
	lease_pml_lease.status,
	Get_last_amount_table.last_amount,
	tenant_pmn_tenant_address.contact_phone,
	tenant_pmn_tenant_address.Contact_fax,
	lease_pml_lease.Original_Start,
	lease_pml_lease.actual_start,
	lease_pml_lease.Original_Lease_end,
	lease_pml_lease.Actual_lease_end,
	lease_pml_lease.Actual_Movein_date,
	lease_pml_lease.actual_moveout_date
from 
	lease_pml_lease
	inner join tenant_pmn_tenant on  primary_tenant = tenant_pmn_tenant.tenant
	inner join TENANT_PMN_TENANT_ADDRESS on primary_tenant = TENANT_PMN_TENANT_ADDRESS.tenant
	inner join 
		(select
			lease_pml_lease_recurring_charge.lease,
			lease_pml_lease_recurring_charge.last_amount,
			max(lease_pml_lease_recurring_charge.{perhaps the date of the recurring charge?}),
		from
			lease_pml_lease_recurring_charge
		where
			lease_pml_lease_recurring_charge.charge_type='Rent'
		group by
			lease_pml_lease_recurring_charge.lease,
			lease_pml_lease_recurring_charge.last_amount
		) as Get_last_amount_table on lease_pml_lease.lease = Get_last_amount_table.lease
where 
	status='Current' 

Open in new window

0
 
looknow12Author Commented:
To the first query, it is better, but there are still duplicates based on the lease field.

The second query comes up with a "General Error"
0
 
looknow12Author Commented:
if I try to restrict the group by to just the lease or maybe the lease and tenant name, I receive this error.

>[Error] Script lines: 1-27 -------------------------
 [TimberlineODBC][TimberlineODBCEngine ODBC Driver]Column invalid. Must be a group by column: property in SELECT LIST. 
0
 
randomjamesCommented:
I'm sure the second one would error out if you didn't change out where I put this;

{perhaps the date of the recurring charge?}

I don't know your tables...I can't fill that in. It was really just supposed to be an example.

I have to ask you again, this is important if you are still getting duplicates; what fields are different in the duplicates? Those, obviously, are the cause of the duplicate records...
0
 
randomjamesCommented:
No, what you tried would not help. Every field that isn't summarized in some way (with a max, sum, count, min, etc) needs to be in the group by section.

Besides, that first example I gave you would give you this highest "last_amount" which may or may not be what you are looking for.
0
 
looknow12Author Commented:
Ahh in the address table, the contact_phone.

Any way to place the first duplicate record's contact field in one column and the next duplicate field in another column.  
0
 
randomjamesCommented:
Here's a way. Notice this will return just one fax number, so if there are two you need to modify this as well. Also, you would still need to fix the line with {perhaps the date of the recurring charge?};
select
        lease_pml_lease.lease,
        tenant_pmn_tenant.name,
        lease_pml_lease.property,
        lease_pml_lease.primary_unit,
        lease_pml_lease.status,
        Get_last_amount_table.last_amount,
        Get_both_phones.contact_phone1,
        Get_both_phones.contact_phone2,
        Get_both_phones.Contact_fax,
        lease_pml_lease.Original_Start,
        lease_pml_lease.actual_start,
        lease_pml_lease.Original_Lease_end,
        lease_pml_lease.Actual_lease_end,
        lease_pml_lease.Actual_Movein_date,
        lease_pml_lease.actual_moveout_date
from 
        lease_pml_lease
        inner join tenant_pmn_tenant on  primary_tenant = tenant_pmn_tenant.tenant
        inner join 
		(select
			tenant_pmn_tenant_address.tenant,
			max(tenant_pmn_tenant_address.Contact_fax) as contact_fax,
			max(tenant_pmn_tenant_address.contact_phone) as contact_phone1,
			min(tenant_pmn_tenant_address.contact_phone) as contact_phone2
		from
			tenant_pmn_tenant_address
		group by
			tenant_pmn_tenant_address.tenant
		) as Get_both_phones on on primary_tenant = Get_both_phones.tenant
        inner join 
                (select
                        lease_pml_lease_recurring_charge.lease,
                        lease_pml_lease_recurring_charge.last_amount,
                        max(lease_pml_lease_recurring_charge.{perhaps the date of the recurring charge?}),
                from
                        lease_pml_lease_recurring_charge
                where
                        lease_pml_lease_recurring_charge.charge_type='Rent'
                group by
                        lease_pml_lease_recurring_charge.lease,
                        lease_pml_lease_recurring_charge.last_amount
                ) as Get_last_amount_table on lease_pml_lease.lease = Get_last_amount_table.lease
where 
        status='Current' 

Open in new window

0
 
looknow12Author Commented:
General Error :-( Is there any punctuation missing?
0
 
randomjamesCommented:
Did you replace this;

    {perhaps the date of the recurring charge?}

with the appropriate date field?

I do see a punctuation error...this line needs to have the comma removed as well;

                        max(lease_pml_lease_recurring_charge.{perhaps the date of the recurring charge?}),
0
 
looknow12Author Commented:
I'm running this.  BTW, I don't care about the date field.  Also, ease_pml_lease_recurring_charge.charge_type can be "Rent"
0
 
looknow12Author Commented:
ohh, I see you have Rent as the charge type.  I've been looking at this through remote desktop and just now adjusted my resolution so I can see more of the query on the screen.  It was tough to look at the entire query.
0
 
looknow12Author Commented:
The other field that should be unique (not sure if this matters, but) and tenant.name, lease.property.

But I am good with the lease or the name only.  If those are unique it is fine.
0
 
Computer101Commented:
Forced accept.

Computer101
Community Support Moderator
0
All Courses

From novice to tech pro — start learning today.