How to select different data based on column values in SQL Server

Question

Currently I have

Payroll table with columns Payroll_ID, Payroll_Name
Paycode table with columns Paycode_ID, PayCode_Desc, Payroll_ID

The Payroll_ID column can be null in "Paycode" table, when it's null, it means All Payrolls, if it's not, then it means a specific payroll in "Payroll" table.

I would like to do things like following (This is pseudo code, just to express my intention):

SELECT 
    PC.Paycode_ID,  
    PC.PayCode_Desc + ' - All Payrolls" AS PayCode_Desc 
FROM 
    Paycode PC 
WHERE
    PC.Payroll_ID = null;

UNION ALL

SELECT 
    PC.Paycode_ID,  
    PC.PayCode_Desc + PR.Payroll_Name AS PayCode_Desc 
FROM
    Paycode PC, Payroll PR 
WHERE
    PC.Payroll_ID != null AND PC.Payroll_ID == PR.Payroll_ID;

How I can achieve this in one statement(important)? Thank you so much in advance! Also please feel free to edit the topic as I'm not sure I phrase it right :)

It's WHERE, not WHEN.... also: you cannot compare NULL with the usual equal or not equal operators - you can only use IS NULL or IS NOT NULL — marc_s
– marc_s, Commented Aug 28, 2015 at 19:26
For your second query: Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Aug 28, 2015 at 19:26
Hi @marc_s I meant to have one statement to achieve, not two separates :) — strisunshine
– strisunshine, Commented Aug 28, 2015 at 19:27
As I mentioned I'm just using pseudo code to express my intention, I don't know how to achieve and that's why come with this question :) — strisunshine
– strisunshine, Commented Aug 28, 2015 at 19:29
For future reference: SQL uses = for equality not ==, but I guess it's just a typo in the last query as you got it right earlier. — jpw
– jpw, Commented Aug 28, 2015 at 19:33

jpw · Accepted Answer · 2015-08-28 19:32:31Z

2

You can simplify this into a single statement:

SELECT 
    PC.Paycode_ID,  
    PC.PayCode_Desc + ISNULL(PR.Payroll_Name, ' - all payrolls') AS PayCode_Desc 
FROM
    Paycode PC
LEFT OUTER JOIN
    Payroll PR ON PC.Payroll_ID = PR.Payroll_ID

If there's no corresponding row in the Payroll table, then PR.Payroll_Name will be NULL and will be replaced with - all payrolls instead.

Also: use proper ANSI/ISO JOIN syntax to make your life easier!

edited Aug 28, 2015 at 19:32

jpw

45k6 gold badges71 silver badges90 bronze badges

answered Aug 28, 2015 at 19:30

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

strisunshine Over a year ago

Thank you, that what I'm looking for : ) You are very knowledgeable, I'll bear in mind about the syntax

drumsta · Accepted Answer · 2015-08-28 19:30:43Z

1

SELECT PC.Paycode_ID
, PC.PayCode_Desc + ISNULL(PR.Payroll_Name, ' - All payrolls') AS PayCode_Desc
FROM Paycode PC
  LEFT JOIN Payroll PR ON PC.Payroll_ID = PR.Payroll_ID

answered Aug 28, 2015 at 19:30

drumsta

1,4042 gold badges17 silver badges27 bronze badges

Comments

jpw · Accepted Answer · 2015-08-28 19:34:43Z

0

I don't see a problem with the first one (except the random double quote)

For the second I would suggest join syntax -- this is quite common and makes things clearer.

Then you can "combine them" with a union all

SELECT 
    PC.Paycode_ID,  
    PC.PayCode_Desc + ' - All Payrolls' AS PayCode_Desc 
FROM Paycode PC 
WHERE PC.Payroll_ID = null;
  UNION ALL
SELECT 
    PC.Paycode_ID,  
    PC.PayCode_Desc + PR.Payroll_Name AS PayCode_Desc 
FROM Paycode P
JOIN Payroll PR ON PC.Payroll_ID = PR.Payroll_ID;

edited Aug 28, 2015 at 19:34

jpw

45k6 gold badges71 silver badges90 bronze badges

answered Aug 28, 2015 at 19:28

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Collectives™ on Stack Overflow

How to select different data based on column values in SQL Server

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related