This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.
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...
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
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
With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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 …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.
Suggested Courses
Course of the Month11 days, 3 hours left to enroll
718 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.