Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help on Joining tables

Author  Topic 

5warriors
Starting Member

2 Posts

Posted - 2015-03-05 : 19:42:29
I have two tables.

Example:

Table1 – Information about users
ID LastName FirstName Department JobDesc
———————————————————————
1 Doe John SQL Team SQL Trainee
2 Doe Jane SQL Team SQL Trainee
3 Vader Darth SQL Dark SQL Master


Table2 – Submitted Information
ID DateSubmitted Department Table1_ID FirstName LastName GradeLevel Grade
—————————————————————————————————————————
22 2015-03-04 00:00:00 SQL Team 2 Jane Doe Low 18
23 2015-03-05 00:00:00 SQL Team 1 John Doe High 22

On table1 are the records of all users.

On table2 are the records of the users who submitted some information per day.

I need to get a report that have the users that have not submitted (NULL) and the users that have submitted per day. In the same report.

I already have something going, but a user who submits on "2015-03-04” and has not submitted on the "2015-03-05" is not seen on the report on the "2015-03-05” as not submitted (NULL) for column “DateSubmitted, GradeLevel, Grade”.

SELECT
Table1.FirstName
,Table1.LastName
,Table1.Deptartment
,Table1.JobDesc
,Table2.DateSubmitted
,Table2.GradeLevel
,Table2.Grade
FROM
Table1
FULL JOIN Table2 ON Table1.ID = Table2.Table1_ID

WHERE
(Table2.DateSubmitted = '2015-03-05 00:00:00' or Table2.DateSubmitted is Null)

Need report(query) to look something like this for the Date Submitted of 2015-03-05
ID LastName FirstName Department JobDesc Date_Submitted GradeLevel Grade
—————————————————————————————————————————
John Doe SQL Team SQL Trainee 2015-03-05 00:00:00 High 22
Jane Doe SQL Team SQL Trainee NULL NULL NULL
Darth Vader SQL Dark SQL Master NULL NULL NULL


Need report(query) to look something like this for the Date Submitted of 2015-03-04
ID LastName FirstName Department JobDesc Date_Submitted GradeLevel Grade
—————————————————————————————————————————
John Doe SQL Team SQL Trainee NULL NULL NULL
Jane Doe SQL Team SQL Trainee 2015-03-04 00:00:00 Low 18
Darth Vader SQL Dark SQL Master NULL NULL NULL


Hopefully someone can help.

Thanks in advance.

jleitao
Posting Yak Master

100 Posts

Posted - 2015-03-06 : 13:49:14
Try this:

SELECT
Table1.FirstName
,Table1.LastName
,Table1.Deptartment
,Table1.JobDesc
,Table2.DateSubmitted
,Table2.GradeLevel
,Table2.Grade
FROM Table1
LEFT JOIN Table2
ON Table1.ID = Table2.Table1_ID
AND Table2.DateSubmitted = '2015-03-05 00:00:00'

------------------------
PS - Sorry my bad english
Go to Top of Page

5warriors
Starting Member

2 Posts

Posted - 2015-03-06 : 17:23:09
Worked Great! Thanks for your help jleitao!!!
Go to Top of Page
   

- Advertisement -