JCoder23 JCoder23 - 1 year ago 50
SQL Question

Filter on Join but still return all rows

Im not sure exactly how to explain this. The best i can explain it is that i want to do something similar to a Left Join, only i want to return a subset of the main table matching criteria of the joined table, but still return all left joined rows. I hope that makes some sort of sense.

I have 4 tables:

Company

Services

ServicesTranslations

CompanyService

A Company can have many Services and a Service can have many Translations.
I want to filter on the ServiceTranslation table for a given search term.

The only way i can get this to work is in the SQL below, but i feel perhaps there is a better more cleaner way to do this, or is my solution ok?

DECLARE @defaultLanguage NVARCHAR(5)

SET @defaultLanguage = (SELECT LanguageCode FROM LANGUAGE WHERE IsDefault = 1)

SELECT
c.Id,
c.Name,
c.OrgNr,
c.UserId,
c.Address,
c.PostalArea,
c.County,
c.Country,
c.Description,
c.DescriptionFull,
c.Telephone,
c.Email,
c.Website,
c.IsApproved,
s.Id,
ISNULL(tr.Name, def.Name) Name

FROM Company c

INNER JOIN CompanyService cs on cs.CompanyId = C.Id

INNER JOIN Service s on s.Id = cs.ServiceId

LEFT OUTER JOIN ServiceTranslation tr
ON s.Id = tr.ServiceId AND tr.LanguageCode = @userLanguage
-- join default language of the service:

LEFT OUTER JOIN ServiceTranslation def
ON s.Id = def.ServiceId AND def.LanguageCode = @defaultLanguage

WHERE c.IsApproved = 1

AND
c.Id IN
(
SELECT c1.Id FROM Company c1

INNER JOIN CompanyService cs1 on cs1.CompanyId = c1.Id

INNER JOIN Service s1 on s1.Id = cs1.ServiceId

INNER JOIN ServiceTranslation tr1
ON s1.Id = tr1.ServiceId AND tr1.LanguageCode = @userLanguage
-- join default language of the service:

INNER JOIN ServiceTranslation def1
ON s1.Id = def1.ServiceId AND def1.LanguageCode = @defaultLanguage



AND s1.IsApproved = 1 AND ISNULL(tr1.Name, def1.Name) LIKE '%' + @searchQuery + '%'
)


Thanks in advance!

EDIT:

I think some clarification is required. Im not sure how to add the table data in a readable format so i have attached some images of the tables and desired results.

Company Table:

enter image description here

Service Table:

enter image description here

ServiceTranslation Table:

enter image description here

CompanyService Table:

enter image description here

Desired Results (@userLanguage = 'nb-NO' and @searchquery = 'Transport'):

There are two companies that have a Service with the word 'Transport' in. I want these two companies returned plus all their other services.

enter image description here

As you can see from the result image, im filter companies that have a matching service, but still want to return those matching companies and all of their services.

I hope this makes it a bit more clear :)

Answer Source

Using CTE:

;WITH Comps AS
(
    SELECT DISTINCT c.CompanyId FROM CompanyService  C 
    LEFT JOIN  ServiceTranslation tr ON tr.ServiceId = C.ServiceId AND tr.LanguageCode = @userLanguage AND tr.Name LIKE '%' + @searchQuery + '%'
    LEFT JOIN ServiceTranslation def ON def.ServiceId = C.ServiceId AND def.LanguageCode = @defaultLanguage AND def.Name LIKE '%' + @searchQuery + '%'
    WHERE tr.ServiceId IS NOT NULL OR def.ServiceId IS NOT NULL
)

    SELECT
        c.Id,
        c.Name,
        c.OrgNr,
        c.UserId,
        c.Address,
        c.PostalArea,
        c.County,
        c.Country,
        c.Description,
        c.DescriptionFull,
        c.Telephone,
        c.Email,
        c.Website,
        c.IsApproved,
        s.Id,
        def.Name

      FROM Company c

      INNER JOIN CompanyService cs on cs.CompanyId = C.Id

      INNER JOIN Service s on s.Id = cs.ServiceId 

      INNER JOIN ServiceTranslation def ON def.ServiceId = s.Id AND def.LanguageCode = @defaultLanguage

      INNER JOIN Comps CM ON CM.CompanyId = c.Id

      WHERE c.IsApproved = 1 AND s.IsApproved = 1

OR with a subquery:

SELECT
            c.Id,
            c.Name,
            c.OrgNr,
            c.UserId,
            c.Address,
            c.PostalArea,
            c.County,
            c.Country,
            c.Description,
            c.DescriptionFull,
            c.Telephone,
            c.Email,
            c.Website,
            c.IsApproved,
            s.Id,
            def.Name

          FROM Company c

          INNER JOIN CompanyService cs on cs.CompanyId = C.Id

          INNER JOIN Service s on s.Id = cs.ServiceId 

          INNER JOIN ServiceTranslation def ON def.ServiceId = s.Id AND def.LanguageCode = @defaultLanguage

          INNER JOIN Comps CM ON CM.CompanyId = c.Id

          WHERE c.IsApproved = 1 AND s.IsApproved = 1 AND c.Id IN 
          (
            SELECT DISTINCT c.CompanyId FROM CompanyService  C 
            LEFT JOIN  ServiceTranslation tr ON tr.ServiceId = C.ServiceId AND tr.LanguageCode = @userLanguage AND tr.Name LIKE '%' + @searchQuery + '%'
            LEFT JOIN ServiceTranslation def ON def.ServiceId = C.ServiceId AND def.LanguageCode = @defaultLanguage AND def.Name LIKE '%' + @searchQuery + '%'
            WHERE tr.ServiceId IS NOT NULL OR def.ServiceId IS NOT NULL
          )
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download