Query tables with same column name

Question

please help

I have 5 temp tables, namely #TEMPTABLE, #TotEmp, #TotEnrol, #NotEnrol and #PercEnrol.

TEMPTABLE contains the following:
Staff Number | Employee_Name | Home_Branch | Enrolledd | Card | Tested
(Enrolledd - indicator of whether employee is enrolled or not - 1/0)

TotEmp contains the following (Total number of employees in branch)
Home_Branch | Employee

TotEnrol contains the following (Total number of enrolled employees in branch)
Home_Branch | Enrolled

NotEnrol contains the following (Total number of employees NOT enrolled in branch)
Home_Branch | Not_Enrolled

PercEnrol contains the following (Percentage of enrolled employees in branch)
Home Branch | Percentage

I would like to do a select which shows the following - AND the percentage enrolled must only be branches under 90% and the results must be grouped by branch.

Home_Branch | Enrolled (from TotEnrol) | Employee (TotEmployee) | Not Enrolled (NotEnrol)| Percentage (PercEnrol)

I wanted to select * from all tables I need and just put that into a final table, but obviously this duplicate Home_Branch keeps popping up and I'm not sure how to join all of them together.

Answer 1

If I understand your question I believe this should work for you:

SELECT     T1.Home_Branch,
           T3.Enrolled,
           T2.Employee,
           T4.NotEnrolled,
           T5.Percentage
FROM       #TEMPTABLE T1
INNER JOIN #TotEmp T2 ON T1.Home_Branch = T2.Home_Branch
INNER JOIN #TotEnrol T3 ON T1.Home_Branch = T3.Home_Branch
INNER JOIN #NotEnrol T4 ON T1.Home_Branch = T4.Home_Branch
INNER JOIN #PercEnrol T5 ON T1.Home_Branch = T5.Home_Branch
WHERE      T5.Percentage < 90

I have used aliases on the tables so you can specified which table you want the column selected to come from, this will eliminate the 5 copies of Home_Branch you mentioned. The table aliases could be named better than T1, T2, T3 etc, but your names of your temp tables are already pretty short!

You also mentioned the percentage being less than 90%, hence the WHERE clause.

As a side note, you probably didn't need to set up all those temp tables (unless you were specifically told to do it like this) depending on your source data you might have been able to use one statement with GROUP BY, sub queries or a couple of CTEs.

Does this answer your question?