CODEWITHSUNDEEP
sqloracle11g
reactFullStackDeveloper
reactFullStackDeveloper

Reputation: 919

LEFT JOIN resulting in duplicate row - ORACLE

I'm writing a sub-query using a WITH clause, and joining it to a main query. The main query has a result set of 332 records, and the sub-query has a result set of 302 records. If I do a LEFT JOIN of the sub-query to the main query I would expect to get 332 but I'm getting 336. I've checked both the main query and sub query for duplicates, specifically of the records being duplicated and duplicates do not exist in the queries independently, s why are they occurring in the join?

WITH FacOwner AS(
    SELECT 
        facility_id_fk, company,
        contact_first_name, contact_last_name,
        contact_title, contact_address1, contact_address2,
        contact_city, contact_state, contact_zipcode_five,
        contact_zipcode_four, contact_phone_number,
        
        sig_first_name, sig_last_name, 
        sig_title, sig_email,
        sig_address1, sig_address2, sig_city, sig_state, 
        sig_zipcode_five, sig_zipcode_four
        
        FROM Contacts
        WHERE Facility_Role = 'Owner'
)

SELECT 
    fg.facility_id_fk,
    fg.general_id_pk,
    fg.facility_name,
    fg.facility_status,
    fg.facility_type,
    fg.landfill_class,
    fg.dshw_staff_person,
    fg.ownership_type,
    loc.county,
    loc.facility_address_street,
    loc.facility_address_city,
    loc.health_department,
    
    FacOwner.company as Owner_Company, (FacOwner.contact_first_name || ' ' || FacOwner.contact_last_name) as OwnerName,  
    FacOwner.contact_title as OwnerTitle, (FacOwner.contact_address1 || FacOwner.contact_address2) as OwnerAddress,
    FacOwner.contact_city as OwnerCity, FacOwner.contact_state as OwnerState, (FacOwner.contact_zipcode_five || '-' || FacOwner.contact_zipcode_four) as OwnerZipcode,
    FacOwner.contact_phone_number as OwnerPhoneNumber,
    
    FacOwner.sig_first_name || ' ' || FacOwner.sig_last_name as OwnerCertSigName,
    FacOwner.sig_title as OwnerCertSigTitle, FacOwner.sig_email as OwnerCertSigEmail,
    (FacOwner.sig_address1 || ' ' || FacOwner.sig_address2) as OwnerCertSigAddress, FacOwner.sig_city as OwnerCertSigCity, FacOwner.sig_state as OwnerCertSigState, 
    (FacOwner.sig_zipcode_five || '-' || FacOwner.sig_zipcode_four) as OwnerCertSigZipcode

FROM facility_general fg
LEFT JOIN facility_location loc ON loc.facility_id_fk = fg.general_id_pk
LEFT JOIN FacOwner on FacOwner.facility_id_fk = fg.general_id_pk
ORDER BY fg.facility_id_fk;

Upvotes: 0

Views: 52

Answers (1)

Lars Skaug
Lars Skaug

Reputation: 1386

Without representative data, I can only help you trouble shoot. To identify the problem, start with the following query.

Do all four columns return the same number?

WITH FacOwner AS(
    SELECT 
        facility_id_fk, company,
        contact_first_name, contact_last_name,
        contact_title, contact_address1, contact_address2,
        contact_city, contact_state, contact_zipcode_five,
        contact_zipcode_four, contact_phone_number,
        
        sig_first_name, sig_last_name, 
        sig_title, sig_email,
        sig_address1, sig_address2, sig_city, sig_state, 
        sig_zipcode_five, sig_zipcode_four
        
        FROM Contacts
        WHERE Facility_Role = 'Owner'
)
SELECT count(*), count(distinct fg.general_id_pk), 
count(distinct FacOwner.facility_id_fk),
count(distinct loc.facility_id_fk)
FROM facility_general fg
LEFT JOIN facility_location loc ON loc.facility_id_fk = fg.general_id_pk
LEFT JOIN FacOwner on FacOwner.facility_id_fk = fg.general_id_pk
ORDER BY fg.facility_id_fk;

Upvotes: 1

Related Questions