CASE / WHEN, IF, JOIN and COALESCE
Posted on 2009-05-04
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...