Blog - Design Web Applications in .NET

Lets start with Easy Stuff - SQL Except CLause
By Martin Zietarski On Thursday, 29 March 2012

Mar
29
20
12

Couple days ago I developed our companys Organization Chart based on simple SQL database table which contains all employees as well objects such are for example printer, fax or departments. Each employee reports to a manager or department.

As we realized that the chart is going to be very wide in the horizontal view - We started to reduce some of the node's and entities.

A SQL statement with the EXCEPT clause saved me a lot work and showed on the other hand that my C# template to draw the nodes - actually works smooth.

Here is the SQL sample code:

SELECT  EmployeeID, LastName, FirstName, ReportToID
FROM    Employee

EXCEPT
SELECT  EmployeeID, LastName, FirstName, ReportToID
FROM    Employee
WHERE   (ReportToID = 123456)

[...]

You can repeat the EXCEPT part of the query as often you need.

So it works like a MINUS to your data set. I was able to reduce my deep sized organziation Chart to a minimum.