Solved: Help with JOIN statement (SQL Server)

I am having trouble setting up a Join statement and am not getting the desired results.

This is for SQL Server.

I have a table for employees and a table for training. The PK on employees = the FK on training.

So something like this:

TblEmployees
PK Name
1 Name1
2 Name2
3 Name3

TblTraining
PK FK Category Expiration
1 1 Category 1
2 1 Category 2
3 2 Category 2
4 2 Category 3
5 3 Category 2

So I want to get the expiration date where Category = "Category 1".

So I have this:

Code:

---


SELECT E.Name, T.Expiration
FROM TblEmployees AS E
LEFT OUTER JOIN
TblTraining AS T ON E.PK=T.FK
WHERE T.Category='Category 1'

---

Now in the example data above only Employee "Name1" has Training "Category 1". And that is the only result that I get. What I want to get is all the employees from the Employees table and if there is no matching training 1 in the Training table then the Expiration field should be Null.

That is what I expected the Left Join to do but it is not returning the results that dont have a match in TblTraining.

I would like to get:

Name1 SomeDate
Name2 Null
Name3 Null

Thanks

DR J

Code:

---

     

SELECT E.Name, T.Expiration
FROM TblEmployees AS E
LEFT OUTER JOIN
TblTraining AS T ON E.PK=T.FK
WHERE T.Category='Category 1' OR T.Expiration is NULL

---

Would return either the records with Category 1 OR any records where the expiration date is null.

You may also want to see the Category1 ,2 3 etc..or the date value would not have too much relevance with no comparison

SQL 2000 or SQL 2005? There are some different functionalities depending on other needs you may have.

I live in SQL Server primarily so let me know if you need help with anything..

Your join is right, but you wouldn't need the OUTER even an INNER join would work if joining on E.[PK] to T.[FK] and return the same results.

YOu would only use a left and/or outer join if you wanted all records from one table which may not have a match to the joining column in the other table.

This is SQL Server 2005.

I'll try this. I think I may have done this already, but note that in the database there is no entry for Category 1. and those are what I want to return the value for still from the first table. So there isnt a category 1 with a null date in the database table 2.

I was doing this from VB.Net, there was no Outer in my SQL, but when I was trying it in the SQL Server Management Studio it adds the Outer to the string.

Will try this in a moment.

Thanks

Maybe I am not clear on the problem or the desired results..

Quote:

---

Now in the example data above only Employee "Name1" has Training "Category 1". And that is the only result that I get. What I want to get is all the employees from the Employees table and if there is no matching training 1 in the Training table then the Expiration field should be Null.

---

My sample would return this information in that the OR says the results either need to have Category 1 or a NULL Expiration field.

There are other ways to handle this depending on what specifically you want to return..

I am active over at www.sqlteam.com where you can find great help on these issues and more for SQL as well (over there I am dataguru1971).


You might need a Case..When column evaluation for the Expiration column if you just want to tag the presence of a date or not...

also see help for isNull and Coalesce as well...

Ok, just tried and it didn't work. I'll try to explain better.

In the Training table I have several items, different categories, some of these expire, some dont. So there could be null values in the expiration date for certain categories.

Now I have a 'First Aid' Category.

So each employee should have a First Aid entry, but new employees wont have this yet. So I want to see who is expiring and also who doesnt have an entry at all.

I was able to do this by first getting the non expiring FKs and making a string and then using NOT IN() to exclude those but it seems cumbersome. Cause I then have to go back a query the employees, then query the expiration dates separately and match them in a report.

So what I would like to do is simply have it return 1 row for each employee in the Employee table. And then one of the fields would be the Expiration_Date from the Training table. If there is no 'First Aid' entry for an employee in the Training table, then that field should be null, and if there is a date then that field should have the date.

This is what I thought Left Join would do automatically, but if there is no rows in the Training table for an employee (for a particular category, or no rows at all) I do not get their row returned from the Employee table.

If that makes sense...

Thanks

Well, I guess it kind of depends, but there are a few ways. You can paste this into your query analyzer and play around with the sample... If you reformat this with some sample and give me a desired result set, I can likely produce what you need.

Sometimes you can do subqueries. like in my last example where I UNION the results of 2 separate queries, 1st one with one criteria, the 2nd one excluding the results of the 1st to get a unique set..

Code:

---

--sample tables and sample data

Create Table #emp (    [PK] int not null,
            [Name] char(20) not null)

Insert INTO #emp (PK,[NAME])
Select 1, 'Peter' UNION ALL
Select 2, 'Paul' UNION ALL
Select 3, 'Mary'

Create Table #train (    [PK] int not null,
            [PK_emp] int not null,
            [Category] char(20) not null,
            [expiration] datetime null)

Insert Into #train (PK,PK_emp, Category, Expiration)
Select 1, 1, 'First Aid', '12/22/2007' UNION ALL
Select 2, 1, 'Other', NULL UNION ALL
Select 3, 2, 'First Aid', '12/22/2007' UNION ALL
Select 4, 2, 'Other', NULL UNION ALL
Select 5, 3, 'Other', NULL

--return all records from #emp, and category and training from expiration
Select #emp.PK, #emp.[name],#train.Category, #train.Expiration
FROM #emp left join #train on #emp.PK = #train.PK_emp

--return only one ROW per employee and either the expiration date if First Aid,
--or NULL if no entry for that employee, or with this sample, employees 1,3,5
Select #emp.PK, #emp.[name],#train.Category, #train.Expiration
FROM #emp left join #train on #emp.PK = #train.PK_emp
WHERE Category = 'First Aid'

--get all employees and either First Aid Expire or Other category with NULL
Select #emp.PK, #emp.[name],#train.Category, #train.Expiration
    FROM #emp inner join #train on #emp.PK = #train.PK_emp
    Where Category = 'First Aid' and Expiration is not null
UNION
Select #emp.PK, #emp.[name],#train.Category, #train.Expiration
    FROM #emp inner join #train on #emp.PK = #train.PK_emp
    Where #emp.PK not in    (Select #emp.PK
                    FROM #emp inner join #train on #emp.PK = #train.PK_emp
                    Where Category = 'First Aid' and Expiration is not null)



Drop Table #emp
Drop Table #train

---

I see where you would have issues getting desired results.

Thanks, doesn't sound like there is a simple query to use and I have a workaround so will mark solved. But some sample data and what I want to get is listed below:

TblEmployees
PK First_Name
1 Peter
2 Paul
3 Mary

TblEmployeesTraining
PK FK Category Expiration_Date
1 1 CPR 12-15-08
2 1 First Aid 12-15-07
3 2 CPR 12-15-08


I would like to get all the rows for each employee where Category = First Aid and Exp Date < 12-31-07 for example.

So all three of these employees should be listed. Peter has a First Aid row, but the exp date is < than 12-31-07. Paul has a row of training, but no rows with the Category = First Aid (so he hasnt taken the training and should be returned). Mary has no training data at all and also needs the training.

So I want to see:

First_Name Expiration_Date
Peter 12-15-07
Paul NULL
Mary NULL

Thanks

Well, that does get tricky, because by specifying Category = 'First Aid', it will only return rows where that is the case, same with expirationdate.

Adding an OR into the mix for expirationdate is null would bring back ALL records with a NULL date, so also not want you want.

Perhaps you could have ANOTHER table which is:

tblTrainingType
PK Desc
1 First Aid
2 CPR
3 VBA
etc...

which would actually be more "normalized" so that you wouldn't have duplicate text descriptions in the training table.

paste this into your QA, see if you get the idea...

Code:

---

--sample tables and sample data

Create Table #emp (    [PK] int not null,
            [Name] char(20) not null)

Insert INTO #emp (PK,[NAME])
Select 1, 'Peter' UNION ALL
Select 2, 'Paul' UNION ALL
Select 3, 'Mary'

Create Table #train (    [PK] int not null,
            [PK_emp] int not null,
            [PK_type] int not null,
            [expiration] datetime null)

Insert Into #train (PK,PK_emp, PK_type, Expiration)
Select 1, 2, 1, '12/22/2007' UNION ALL  -- Paul First Aid
Select 2, 1, 1, NULL UNION ALL --Peter CPR
Select 3, 2, 1, '12/22/2008' UNION ALL --Paul CPR
Select 4, 3, 1, NULL UNION ALL
Select 5, 3, 2, NULL

Create Table #type (PK int not null, [desc] char(10) not null)
Insert Into #type (PK, [Desc])
Select 1, 'First Aid' UNION ALL
Select 2, 'CPR' UNION ALL
Select 3, 'VBA' UNION ALL
Select 4, 'SQL'

--return all employees, expiredate < 12/31/2007 OR no expire date for first aid.

Select  #emp.[name],#train.Expiration
From #emp left join #train on #emp.PK = #train.PK_emp
          left join #type on #train.PK_type = #type.PK
WHERE (#train.expiration <='12/31/2007' or #train.expiration is null) and #type.[Desc] = 'First Aid'
Drop Table #emp
Drop Table #train
Drop Table #type

---

by using another control table, it is more normalized, and by linking to the description (although it does not really change the parameters you describe)... the resulting query returns the specified records...although requires filtering for the TYPE of training just the same. There are ways to make this accept variables for the filters...so there is always a way to simplify it..