CASE / WHEN, IF, JOIN and COALESCE
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...
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 one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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)?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.