Solved

SQL Join question

Posted on 2012-04-10
10
268 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
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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now