Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

distinct records using join

distinct records using join

Explorer

Hi,

 

I am trying to get distinct records using join and where conditions.

 

But I got duplicate records while am using join query.

 

Here's my query can any one please help me into this.

 

select jobs.Name as jobNumber,swo.Workorder_VIN_Number__c as vin,swo.Workorder_Technician_Names__c as technicianNames,
swo.Name as serviceNumber ,swo.Service_Date__c as serviceDate,swo.Scheduled_Service_Name__c as scheduledServiceName ,
swo.ssId_Accuracy__c as scheduledServiceIdAccuracy,swo.Scheduled_Service_Description__c as scheduledServiceDescription
,swo.Workorder_Dealer_Name__c as dealerName
,swo.Service_Dealership_Code__c as dealerCode,swo.Workorder_Advisor_Name__c as advisorName,Jobs.Jobs_Description__c as jobDescription
from swo join Jobs on
swo.id=Jobs.JOBS_WORKORDER__C
join qrsContact on qrsContact.id=swo.WORKORDER_CONTACT__C and
qrsContact.FirstName=swo.Workorder_Contact_Firstname__c and qrsContact.LastName=swo.Workorder_Contact_Lastname__c
join qrscvo on qrscvo.Contact_Name__c=qrsContact.id
where qrsContact.Email= '___' and qrsContact.MYAUDI__C='__'
and qrsContact.SOURCE__C='___' and qrscvo.Customer_vehicle_Relationship_Status__c ='___' and qrscvo.customer_vehicle_relationship__c = '____'

1 REPLY 1

Re: distinct records using join

Master Collaborator

Thanks for the question.

 

I'm not totally sure what the cause of your problem is - hard to tell without an example of the results returned versus what you expected it to return. Do you mean that there are full rows returned that are identical to each other, or do you mean that some of the original rows from the table were joined multiple times with other rows (i.e. if you put a 'select distinct' instead of a 'select' does that give you the result you hoped for?)

 

My guess is either:

 

  1. There are duplicates in one or more of the tables that also show up in the output. You could add a 'distinct' to the query or deduplicate the underlying table (if it's not meant to have duplicates).
  2. You are missing a condition on one of the joins and you're joining rows that you didn't intend to join.