SQL Join question

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?
LVL 1
HLRosenbergerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
please post your table structure, sample data and the required output
0
HLRosenbergerAuthor Commented:
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
keyuCommented:
SELECT tRecords.EmployeeID, Orders.Category
 FROM tRecords
 LEFT JOIN Categories
 ON tRecords.Category=Orders.Category
 ORDER BY tRecords.EmployeeID
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HLRosenbergerAuthor Commented:
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
HLRosenbergerAuthor Commented:
How about a union, where one SELECT of the union is a NOT type of select?
0
agusacilCommented:
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
agusacilCommented:
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
HLRosenbergerAuthor Commented:
agusacil - thanks.  How would I modify this for  a single employee (empuid)?
0
agusacilCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HLRosenbergerAuthor Commented:
Thanks!   That did the trick.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.