Solved

SQL Join question

Posted on 2012-04-10
10
274 Views
Last Modified: 2012-04-11
I have two tables - training categories and training records.   I LEFT JOIN the two tables using the category name of the training which exists in both tables.  The training records are for all employees. I want a two queries - one for all employee and one for individual employees - that will give me a set of records for all training categories and NULLs for the training record columns if they do not exist.  A normal LEFT JOIN.  I want to know all the trainings taken as well as those not taken.  

However, I cannot get this to work, I believe because across all employees, all training categories have been accounted for.   I do not get records with NULL columns for the training record.  How can I correct this?  Do I have to group?
0
Comment
Question by:HLRosenberger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37830405
please post your table structure, sample data and the required output
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 37830451
Training Categories Table

ID         Name
1         Category1
2         Category2
3         Category3
4         Category4


Training Records table
ID         EmployeeID            Category
1            1111                       Category1
2            1111                       Category2
3            2222                       Category4
4            3333                       Category3


Results

EmployeeID             Category    
                         
 1111                       Category1                            
 1111                       Category2
 1111                       NULL
 1111                       NULL

 2222                       NULL
 2222                       NULL
 2222                       NULL
 2222                       Category4

3333                        NULL
3333                        NULL
3333                        Category3                              
3333                        NULL
0
 
LVL 9

Expert Comment

by:keyu
ID: 37831988
SELECT tRecords.EmployeeID, Orders.Category
 FROM tRecords
 LEFT JOIN Categories
 ON tRecords.Category=Orders.Category
 ORDER BY tRecords.EmployeeID
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 1

Author Comment

by:HLRosenberger
ID: 37832276
Something is not quite right.  And maybe I have not articulated correctly.  See attached.  There are 7 categories.  For one specific employee, they have 5 training records - 2 of which are for the same category.   So, for results I want 8 records - the 5 training records, and 3 NULL records because this employee has no training records for 3 categories.
categories.png
trainingrecs.png
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 37832568
How about a union, where one SELECT of the union is a NOT type of select?
0
 
LVL 4

Expert Comment

by:agusacil
ID: 37832614
select b.emplid,c.category from training c right outer join
 (select e.emplid,t.category  from Training t cross join (select distinct(emplid) from  Training t1) as e ) as b
on c.emplid = b.emplid and c.category = b.category       
      order by b.emplid asc
0
 
LVL 4

Expert Comment

by:agusacil
ID: 37832629
Sorry that was not accurate,
Try this

select b.emplid,c.category from training c right outer join 
(select e.emplid,t.category  from Trainingcategory t cross join (select distinct(emplid) from  Training t1)as e
	 ) as b
on c.emplid = b.emplid and c.category = b.category 	
	order by b.emplid asc

Open in new window

0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 37832649
agusacil - thanks.  How would I modify this for  a single employee (empuid)?
0
 
LVL 4

Accepted Solution

by:
agusacil earned 500 total points
ID: 37832711
Hi HLRosenberge,

You can remove the last 'order by' clause and change it to 'where clause' so it become :

select b.emplid,c.category from training c right outer join 
(select e.emplid,t.category  from Trainingcategory t cross join (select distinct(emplid) from  Training t1)as e
	 ) as b
on c.emplid = b.emplid and c.category = b.category 	
	where b.emplid = '1111'

Open in new window

0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 37832911
Thanks!   That did the trick.
0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question