CODEWITHSUNDEEP
sqlsql-server
Unknown Coder
Unknown Coder

Reputation: 6731

SQL Server 2008 JOIN Question

I have four tables one is a master table that contains columns for EmployeeID, VendorID and DriverID

Then I have corresponding tables for each of these: employees, vendors and drivers and they all have similar columns like first name, last name, etc.

So, I want to be able to pull all records from my master table and have the corresponding first name and last based on whether the master record is an employee, vendor or driver.

A row in the master table would like this for an employee

EmployeeID = 352
VendorID = 0
DriverID=0

Vendor would look like

EmployeeID = 0
VendorID = 954
DriverID=0

etc.

Help?

Upvotes: 2

Views: 151

Answers (7)

Nikshep
Nikshep

Reputation: 2115

I dont the reason why this design was required. But this should work even if a single row has both EmplyeeID and VendorID as not 0

SELECT 'employee' AS src, EmployeeID AS ID, FirstName, LastName FROM mastertable JOIN employee ON mastertable.EmployeeID = employee.ID where mastertable.EmployeeID != 0 UNION SELECT 'vendor' AS src, VendorID AS ID, FirstName, LastName FROM mastertable JOIN vendor ON mastertable.VendorID = vendor.ID where mastertable.VendorID != 0 UNION SELECT 'driver' AS src, DriverID AS ID, FirstName, LastName FROM mastertable JOIN driver ON mastertable.DriverID = driver.ID where mastertable.DriverID != 0

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 837946

You could use UNION ALL:

SELECT 'employee' AS src, EmployeeID AS ID, FirstName, LastName
FROM mastertable
JOIN employee
ON mastertable.EmployeeID = employee.ID

UNION ALL

SELECT 'vendor' AS src, VendorID AS ID, FirstName, LastName
FROM mastertable
JOIN vendor
ON mastertable.VendorID = vendor.ID

UNION ALL

SELECT 'driver' AS src, DriverID AS ID, FirstName, LastName
FROM mastertable
JOIN driver
ON mastertable.DriverID = driver.ID

This will work even if someone has more than one ID set (in this case they will get one row for each ID that is set).

You might also want to consider if it would be worth changing your schema to make it easier to query. Could you move all the common columns into the master table? Then you would only need to write the following query to get what you want:

SELECT ID, Type, FirstName, LastName, ...
FROM mastertable

Upvotes: 4

Karel
Karel

Reputation: 2212

This works:

SELECT (case when m.CustId > 0 then c.FirstName else case when m.EmpId > 0 then emp.FirstName end end) as FirstName, * FROM MAster m Left Join Employee emp ON m.EmpId = emp.EmpId Left Join Customer c ON m.CustId = c.CustIdster table to a Union of all the detail tables

Upvotes: 0

xelco52
xelco52

Reputation: 5347

Jim,

This can be accomplished via unions and left joins onto the master table, but I would like to suggest that you may want to look at the design. It seems that vendors, employees and drivers are all people - with things like first names, last names, addresses, phone numbers...etc. It seems that a "person" table would be appropriate to hold all this information, with additional tables that store information specific to their individual roles.

Your master table can then have a field for personID to join in generic people information, and another field(s) for an ID that can tie into role specific data.

Let me know if you'd like some clarity on this. With more information regarding your design needs, we can dig deeper.

Upvotes: 2

Chris B. Behrens
Chris B. Behrens

Reputation: 6297

This assumes that the other two id fields are null for a particular type:

SELECT NT.FirstName, NT.LastName FROM People P JOIN MasterTable MT ON P.Id = MT.EmployeeID
JOIN Drivers NT ON NT.Id = MT.DriverID WHERE DriverID IS NOT NULL
UNION
SELECT NT.FirstName, NT.LastName FROM People P JOIN MasterTable MT ON P.Id = MT.EmployeeID
JOIN Vendors NT ON NT.Id = MT.VendorID WHERE VendorId IS NOT NULL
UNION
SELECT NT.FirstName, NT.LastName FROM People P JOIN MasterTable MT ON P.Id = MT.EmployeeID
JOIN Employees NT ON NT.Id = MT.DriverID WHERE EmployeeID IS NOT NULL

You could also redesign your master type to have a PersonType field, and store a single id, avoiding the sparse data inherent in this design.

Upvotes: 0

Ken Downs
Ken Downs

Reputation: 4827

You probably want COALESCE, and methinks those zero id's maybe should be null, but anyway:

select master.id
     , coalesce(emp.first_name,vend.first_name,drv.first_name)
       as first_name
     , -- repeat for other columns
  from master
  left
  join employee emp on master.employeeId = emp.employeeId
  left
  join vendor  vend on master.vendorId   = vend.vendorId
  left
  join driver   drv on master.DriverId   = drv.driverId

This assumes that a person will be in only one of the three.

On a second branch of thought, if a person can only be in one of the three, this looks like a supsicious table design, perhaps there should only be one child table, but that would depend on what else is going on.

Upvotes: 3

codingbadger
codingbadger

Reputation: 43974

You could to do this using Left Joins and case statements but it would be horrible to maintain.

I would suggest that you write select queries for employees, vendors and drivers and then use a Union to concatenate the results and wrap the whole thing in to a view.

Upvotes: 0

Related Questions