Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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?
0
HLRosenberger
Asked:
HLRosenberger
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
HLRosenbergerAuthor Commented:
Thanks!   That did the trick.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now