Query two tables with out the same number of records on the second table.

I have 2 tables, one with the columns

ClientForms
ClientFormID | Name | Description | FileName | ClientID
1                      Form1  Des1             f1.pdf           1901
2                      Form2  Des2             f2.pdf           1901
3                      Form3  Des3             f3.pdf           1901
4                      Form4  Des4             f4.pdf           1901
5                      Form5  Des5             f5.pdf           1901
6                      Form10  Des10         f10.pdf         1905
7                      Form11  Des11          f11.pdf         1905

and another table with the data saved for each form with the columns
FormsData
FormID | ClientID | ClientFormID | EmplID | Completed
1                1901              1                 2               Yes
2                1901              2                 2               Yes
3                1901              3                 3               Yes
4                1901              2                 3               Yes
5                1905              6                 4               Yes
6                1905              7                 4               Yes


I need to query both tables to show the forms belonging to the client for a specific employee and show whether or not the employee has completed the form. My problem is that id the employee has not yet completed the form, the record does not exist on the FormsData table.

The results I need to show if employe with id 2 and clientid 1901 are as follows

Name | Description | Completed
Form1   Des1                Yes
Form2   Des2                Yes
Form3   Des3                No
Form4   Des4                No
Form5   Des5                No

Thanks for the help in advance.
-Ben
Benjamin_Asked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
or
select name, description, COALESCE(completed, 'No') [Completed]
from ClientForms A
left join FormsData B on (A.ClientFormID = B.ClientFormID and A.ClientID = B.ClientID)
where B.EmplID = 2

Open in new window

0
 
Ephraim WangoyaCommented:
use left join
select name, description, ISNULL(completed, 'No') [Completed]
from ClientForms A
left join FormsData B on (A.ClientFormID = B.ClientFormID and A.ClientID = B.ClientID)

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
If you use b.emplid in the where clause you are essentially doing an inner join, so you will want any criteria on second table to be un join criteria.
0
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.

 
Anthony PerkinsCommented:
In other words this:
select name, description, COALESCE(completed, 'No') [Completed]
from ClientForms A
left join FormsData B on (A.ClientFormID = B.ClientFormID and A.ClientID = B.ClientID)
where B.EmplID = 2

Is the same as this:
select name, description, COALESCE(completed, 'No') [Completed]
from ClientForms A
INNER join FormsData B on (A.ClientFormID = B.ClientFormID and A.ClientID = B.ClientID)
where B.EmplID = 2

When you probably mean:
select name, description, COALESCE(completed, 'No') [Completed]
from ClientForms A
left join FormsData B on (A.ClientFormID = B.ClientFormID and A.ClientID = B.ClientID) and B.EmplID = 2
0
 
Kevin CrossChief Technology OfficerCommented:
Thanks, Anthony. I was on mobile device earlier and had to keep the words sparse. Definitely needed a more clear example.
0
 
Anthony PerkinsCommented:
>>I was on mobile device earlier and had to keep the words sparse. <<
I figured as much.  I thought I would demostrate that occassionaly I can put more than two words together. :)
0
All Courses

From novice to tech pro — start learning today.