Improve company productivity with a Business Account.Sign Up

x
?
Solved

CASE / WHEN, IF, JOIN and COALESCE

Posted on 2009-05-04
7
Medium Priority
?
588 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 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 143

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
A proven path to a career in data science

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.

 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1000 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

Free Tool: Path Explorer

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

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.

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.

Join & Ask a Question