Benjamin_
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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
Thanks, Anthony. I was on mobile device earlier and had to keep the words sparse. Definitely needed a more clear example.
>>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. :)
I figured as much. I thought I would demostrate that occassionaly I can put more than two words together. :)
Open in new window