Solved

CASE / WHEN, IF, JOIN and COALESCE

Posted on 2009-05-04
7
550 Views
Last Modified: 2012-05-06
Hi,

I have three tables: Users, Customers and Staff

Users contains three columns: ID_Users, ID_Customers, and ID_Staff. ID_Users is the identity and primary key. In each row, either ID_Customers or ID_Staff will have a value, but never both.

Both Customers and Staff have columns named firstName and lastName as well as an identity field (ID_Staff / ID_Customers).

I want to be able to parse the Users table and get the following information back:
ID_Users, firstName, lastName and a text field identifying whether the row is a staff member or customer. Obviously, the source of firstName and lastName will differ depending on whether the user if a customer or staff member.

I just have no idea how to put it all together...
0
Comment
Question by:Sanx69
  • 3
  • 2
  • 2
7 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24298566

something like this...

select u.id_users, u.id_customers, coalesce(c.firstname, s.firstname) as firstname, coalesce(c.lastname, u.lastname)....
from users u
left join customers c on u.id_users
left join staff s on u.id_staff = s.id_staff

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 24298570
this should do
SELECT u.ID_users, ISNULL(c.firstname, s.firstname) firstname, isnull(c.lastname, s.lastname) lastname
  FROM users u
  LEFT JOIN Customers c
    ON c.ID_Customer = u.ID_Customer
  LEFT JOIN Staff s
    ON s.ID_Staff = u.ID_Staff

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24298575
now, the obvious question is:
why don't you keep the "common" data (list firstname/lastname) in the central table (users)?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 250 total points
ID: 24298602
quick correction to mine...my fingers went to fast for me.

select u.id_users, u.id_customers, coalesce(c.firstname, s.firstname) as firstname, coalesce(c.lastname, u.lastname)....
from users u
left join customers c on u.id_customer = c.id_customer
left join staff s on u.id_staff = s.id_staff
0
 
LVL 3

Author Closing Comment

by:Sanx69
ID: 31577725
Thank you. The solutions differ only on the use of ISNULL versus COALESCE, and both solutions work just fine, so I've split the points evenly between you. Thanks very much for your help.
0
 
LVL 3

Author Comment

by:Sanx69
ID: 24298959
@angelll

It's a good question. The Staff and Customers table contain a lot more besides just the names and most of the fields are not common. For the vast majority of operations, I'd be working on one table or the other; it's only select operations that I have to consider them jointly.
0
 
LVL 3

Author Comment

by:Sanx69
ID: 24299092
Ooops. Didn't notice this was missing from both solutions. I was also looking for a method of determining whether the names returned were of a customer of staff member. And I managed to work it out all on my own, to my surprise:

SELECT    u.ID_users,

          ISNULL(c.firstname, s.firstname) firstname,

          ISNULL(c.lastname, s.lastname) lastname,

          CASE

              WHEN u.ID_Customer IS NULL

              THEN 'Staff' ELSE 'Customer'

              END customerType

FROM users u

LEFT JOIN Customers c

     ON c.ID_Customer = u.ID_Customer

LEFT JOIN Staff s

     ON s.ID_Staff = u.ID_Staff

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now