Filtering by a secondary value if the first value isn't present in SQL Query-CodePudding

I have a form that shows addresses. Most companies also have a "Physical" and "Postal" address. The form currently has the company showing twice for each address where they have a physical and postal. I can't seem to find a way to filter the form so that it checks to see if the company has a Physical address and only displays that record and if it does not then it will display the postal address.

I had assumed it would be a where condition on the "AddressType" for if it equals "Physical" else display postal, but as of yet I have not tried something that works.

SQL Query

SELECT tblAddress.SupplierID, tblAddress.AddressType, tblAddress.Address, tblAddress.[Town/Suburb], tblAddress.Region, tblSuppliersAgreements.AgreementID, tblSuppliers.IsActive
FROM (tblSuppliers INNER JOIN tblAddress ON tblSuppliers.[ID] = tblAddress.[SupplierID]) 
INNER JOIN tblSuppliersAgreements ON tblSuppliers.[ID] = tblSuppliersAgreements.[SupplierID];

Update 1

Updated the Query in the builder to match a below answer but it was missing () around the first inner join which was why it was giving me the syntax error. I can't seem to now find where it is giving me the error for this time. It says it is between the following tblAddress.AddressType= (CASE WHEN EXISTS(SELECT 1 FROM tblAddress WHERE tblAddress.AddressType='Physical' and tblAddress.[SupplierID]=tblSuppliers.[ID]) THEN 'Physical' ELSE 'Postal' END) and once I hot Okay it Highlights the WHEN part.

Updated Query

SELECT tblAddress.SupplierID, tblAddress.AddressType, tblAddress.Address, tblAddress.[Town/Suburb], tblAddress.Region, tblSuppliersAgreements.AgreementID, tblSuppliers.IsActive
FROM (tblSuppliers 
INNER JOIN tblAddress ON tblSuppliers.[ID] = tblAddress.[SupplierID]) 
INNER JOIN tblSuppliersAgreements ON tblSuppliers.[ID] = tblSuppliersAgreements.[SupplierID]
WHERE tblAddress.AddressType=
             (CASE WHEN EXISTS(SELECT 1 
                                                FROM tblAddress 
                                                WHERE tblAddress.AddressType='Physical' and tblAddress.[SupplierID]=tblSuppliers.[ID]) 
                                    THEN 'Physical'
                                    ELSE 'Postal'
                                    END)

Shortened but listed the above incase this contains an error

SELECT ta.SupplierID, ta.AddressType, ta.Address, ta.[Town/Suburb], ta.Region, tsa.AgreementID, ts.IsActive
FROM (tblSuppliers ts
INNER JOIN tblAddress ta ON ts.[ID] = ta.[SupplierID])
INNER JOIN tblSuppliersAgreements tsa ON ts.[ID] = tsa.[SupplierID]
WHERE ta.AddressType=
             (CASE WHEN EXISTS (SELECT 1
                                                FROM tblAddress
                                                WHERE AddressType='Physical' and SupplierID=ts.[ID])
                                    THEN 'Physical'
                                    ELSE 'Postal'
                                    END)

CodePudding user response：

Try this using case statement to check if physical address exists then filter on physical address else postal address

SELECT ta.SupplierID, ta.AddressType, ta.Address, ta.[Town/Suburb], ta.Region, tsa.AgreementID, ts.IsActive
FROM tblSuppliers ts INNER JOIN tblAddress ta ON ts.[ID] = ta.[SupplierID] 
INNER JOIN tblSuppliersAgreements tsa ON ts.[ID] = tsa.[SupplierID]
where ta.AddressType= case when exists(select 1 from tblAddress where AddressType='Physical' and SupplierID=ts.id) then 'Physical' else 'Postal' end

CodePudding user response：

Your current syntax error happens because Access SQL does not support CASE ... WHEN. You need a different strategy.

Sounds like your AddressType field contains either "Physical" or "Postal". And you want the "Physical" row for each supplier if it exists; otherwise the "Postal" row.

Since "Physical" sorts before "Postal", this query will return "Physical" as preferred_address for any supplier which includes a row with "Physical" in AddressType.

SELECT
    SupplierID,
    Min(AddressType) AS preferred_address
FROM tblAddress
GROUP BY SupplierID

Assuming that query identifies the supplier addresses you want, INNER JOIN it back to tblAddress to select only the matching rows.

SELECT
    adrs.SupplierID,
    adrs.AddressType,
    adrs.Address,
    adrs.[Town/Suburb],
    adrs.Region
FROM
    tblAddress AS adrs
    INNER JOIN
    (
        SELECT
            SupplierID,
            Min(AddressType) AS preferred_address
        FROM tblAddress
        GROUP BY SupplierID
    ) AS sub
    ON adrs.SupplierID = sub.preferred_address;

And assuming that still gives you only the addresses you want, adapt your original query to use this one in place of tblAddress.