Solved

SQL Join question

Posted on 2012-04-10
10
272 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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