Combining tables in SQL/QlikView

Is it pos开发者_StackOverflow社区sible to combine 2 tables with a join or similar construct so that all non matching field in one group. Some thing like this: All employees with a department name gets their real department and all with no department ends up in group "Other".

Department: SectionDesc ID

Dep1 500

Dep2 501

Employee: Name ID

Anders 500

Erik 501

root 0

Output: Anders Dep1

Erik Dep2

root Other

Best Regards Anders Olme

What you are looking for is an outer join:

 SELECT e.name, d.name
 FROM employee e 
 LEFT OUTER JOIN departments d ON e.deptid = d.deptid

This would give you a d.name of NULL for every employee without a department. You can change this to 'Other' with something like this:

CASE WHEN d.name IS NULL THEN 'Other' Else d.name END 

(Other, simpler versions for different DBMSs exist, but this should work for most.)

QlikView is a bit tricky, as all joins in QlikView are inner joins by default. There is some discussion in the online help about the different joins, short version is that you can create a new table based on different joins in the script that reads in your data. So you could have something like this in your script:

Emps: SELECT * FROM EMPLOYEES;
Deps: SELECT * FROM DEPARTMENTS;
/* or however else you get your data into QlikView */

EmpDep:
SELECT Emps.name, Deps.name
FROM EMPS LEFT JOIN Deps

In order for this join to work the column names for the join have to be the same in both tables. (If necessary, you can construct new columns for the join when loading the base tables.)