Query: Comparing Two Tables (1 Viewer)

[Psilokan]

I'm trying to write a report which initially I didnt think would be hard to do, but when it came time to write the underlying query I realized it was a bit above me.

I cant give away too much of the database structure here, due to legal issues, so sorry for being vauge. Hopefully I can give enough information that someone can get me started.

I have three tables that will be used in this query. One is called NAMES and has all of personal details of contacts. The only fields that are needed from this table are the primary key and citizenship field.

The other two tables are virtually the same, and they are the two being compared. One is REGISTRATION and the other APPLICATION. Essentially what I want here is to be able to compare the number of who applied to the number who actually registered.

Here are the pseudo-tables to give you a better picture:

NAMES
nID
FName
LName
DoB
Citizenship

REGISTERED
programCode
startDate
endDate
nID (foreign key)

APPLIED
programCode
startDate
endDate
nID (foreign key)

I've tried a few different approaches which worked in my head, but when it came time to run the query the results were different. Any suggestions on how I should tackle this?

 

[FoFa]

If you just want a single total try:
SELECT COUNT(N.nID) as NumberPeople, COUNT(R.nID) as NumberRegistered, COUNT(A.nID) as NumberApplied
FROM Names N
LEFT JOIN REGISTERED R on R.nID = N.nID
LEFT JOIN APPLIED A on A.nID = N.nID

 

[Psilokan]

Sadly as soon as I try to left join the second table it all falls apart. I get a missing operator error. I've also tried inner joins, as well as the where joins (not sure what those are called) and in both of those cases its returning the same values for both the registered and applied counts...

arg!

 

[FoFa]

Your joins should be between the names table and each of the others. The Registered table should NOT be joined in anyway to the Applied table.
Using your example and my query it worked fine for me. Consider i2 in my example the same as nID in yours.