Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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
0
Benjamin_
Asked:
Benjamin_
  • 2
  • 2
  • 2
1 Solution
 
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
 
Ephraim WangoyaCommented:
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
 
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now