At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.
Solved
CASE / WHEN, IF, JOIN and COALESCE
Posted on 2009-05-04
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...
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...
-
3
2
2
7 Comments
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
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
now, the obvious question is:
why don't you keep the "common" data (list firstname/lastname) in the central table (users)?
why don't you keep the "common" data (list firstname/lastname) in the central table (users)?
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
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
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.
@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.
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.
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
Featured Post
An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs).
Being someone who is always looking for alternatives to "work your data", I came across this …
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Course of the Month11 days, left to enroll
608 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.