CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Justin Kright
Justin Kright

Reputation: 65

SQL Joining tables with 'constants'

I have a table of orders,

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

And a table of email groups, 

Role              Email 
-----------------------------------------------------
Filtered_Group    [email protected];[email protected]

When Location = 001, SalesPersonEmail must be the Email field from Filtered_Group

SalesPersonEmail for all other locations must be "[email protected];" + the Email for Role No_Filter_Group.

I'm currently using the following to achieve this, 

SELECT i.Invoice, i.Location, i.[Customer Code], 
    CASE WHEN i.Location = 001 
         THEN f.Email 
         ELSE N'[email protected];' + 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 the Role No_Filter_Group may not exist in the Role table at times, which causes the above query to return nothing.

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

Thanks

Upvotes: 4

Views: 5668

Answers (5)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

A relatively simple way is to use LEFT JOIN and put the special number 001 for your location and special role names Filtered_Group and No_Filter_Group in the join condition.

In this SQL Fiddle you can comment/uncomment one line in the schema definition to see how it works when RoleCodes has a row with No_Filter_Group and when it doesn't.

In any case, the query would return all rows from Invoice table.

SELECT
  Invoice.Invoice
  ,Invoice.Location
  ,Invoice.[Customer Code]
  ,CASE WHEN Invoice.Location = '001'
  THEN RoleCodes.Email
  ELSE '[email protected];' + 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')

Upvotes: 1

Banu Priya
Banu Priya

Reputation: 61

use the following Query:

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

Upvotes: 0

Mihir Shah
Mihir Shah

Reputation: 988

Nested select will be run for each row, instead, you could try this :-

Select   i.Invoice
        ,i.Location
        ,i.CustomerCode
        ,Isnull(r.Email,'[email protected]') 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

Upvotes: 0

Chizzle
Chizzle

Reputation: 1715

Left join or an easier, albeit less efficient method would be to do a subquery in the select statement 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'[email protected];' + ISNULL( (SELECT nf.Email as SalesPersonEmail FROM  RoleCodes nf WHERE nf.Role = N'No_Filter_Group'), '')
    END
FROM   Invoice i

Normally you would want to join these in on each other but I'm not certain how you would do that with the schema provided.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

Try something like this.

Note: This is just a example am not sure about the tables and column of your schema. Replace with the respective tables and columns

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

If email_table table will have only one row for [role] = 'Filtered_Group' then you can remove the TOP 1 from the sub-query

Upvotes: 1

Related Questions