SQL Joining Tables with Constants

Question

I have an order table,

Invoice   Location    Customer Code   SalesPersonEmail
------------------------------------------------------
300001    001         CUS001          ?
300002    006         CUS002          ?

And the email group table,

Role              Email 
-----------------------------------------------------
Filtered_Group    [e-mail];[e-mail]

When Location = 001, SalesPersonEmail must be an email field from Filtered_Group

SalesPersonEmail for all other locations must be " [e-mail] ;" + Email for the No_Filter_Group role.

I am currently using the following for this:

SELECT i.Invoice, i.Location, i.[Customer Code], 
    CASE WHEN i.Location = 001 
         THEN f.Email 
         ELSE N'[e-mail];' + nf.Email as SalesPersonEmail
    END
FROM   Invoice i, RoleCodes f, RoleCodes nf
WHERE  f.Role = N'Filtered_Group' AND nf.Role = N'No_Filter_Group'

My problem is that the role of No_Filter_Group sometimes cannot exist in the role table, which leads to the fact that the above query returns nothing.

How can I join these tables correctly, so if No_Filter_Group does not exist in the table, rows that have SalesPersonEmail from Filtered_Group are still returned from the query?

thank

Answer 1

- LEFT JOIN 001 Filtered_Group No_Filter_Group .

SQL Fiddle / , , , RoleCodes No_Filter_Group, .

Invoice.

SELECT
  Invoice.Invoice
  ,Invoice.Location
  ,Invoice.[Customer Code]
  ,CASE WHEN Invoice.Location = '001'
  THEN RoleCodes.Email
  ELSE '[e-mail];' + ISNULL(RoleCodes.Email, '')
  END AS SalesPersonEmail
FROM
  Invoice
  LEFT JOIN RoleCodes ON
    (Invoice.Location = '001' 
    AND RoleCodes.Role = 'Filtered_Group')
    OR
    (Invoice.Location <> '001' 
    AND RoleCodes.Role = 'No_Filter_Group')

Answer 2

- .

.. , .

SELECT CASE 
         WHEN location = '001' THEN (SELECT TOP 1 email 
                                     FROM   email_table 
                                     WHERE  [role] = 'Filtered_Group') 
         ELSE '[e-mail];' 
       END 
FROM   orders 

email_table [role] = 'Filtered_Group', TOP 1 sub-query

Answer 3

A left join or a simpler, albeit less effective method is to make a subquery in the ad itself.

SELECT i.Invoice, i.Location, i.[Customer Code], 
    CASE WHEN i.Location = 001 
             THEN (SELECT TOP 1 f.Email FROM RoleCodes f WHERE f.Role = N'Filtered_Group')
         ELSE N'[e-mail];' + ISNULL( (SELECT nf.Email as SalesPersonEmail FROM  RoleCodes nf WHERE nf.Role = N'No_Filter_Group'), '')
    END
FROM   Invoice i

Usually you would like to join them with each other, but I'm not sure how you will do this with the provided scheme.

Answer 4

Nested selection will be launched for each row, instead you can try the following: -

Select   i.Invoice
        ,i.Location
        ,i.CustomerCode
        ,Isnull(r.Email,'[e-mail]') As SalesPersonEmail
From    Invoice As i With (Nolock)
        Left Join 
        (
            Select   rc.Email
                    ,'001' As Location
            From    RoleCodes As rc With (Nolock)
            Where   rc.Role = 'Filtered_Group'
        ) As r On i.Location = r.Location

Answer 5

use the following query:

      select t.Invoice,t.Location,t.[Customer Code],
      case t.Location
      when '001' then 
           t2.Email
      else
          '[e-mail]'
      end
      as 
      Salespersonemail
      from orders t
      join email_groups t2 on t2.Role='Filtered_Group'