Help on Joining tables - SQL Server Forums

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

5warriors
Starting Member

2 Posts

Posted - 2015-03-06 : 17:23:09

Worked Great! Thanks for your help jleitao!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources