Testing if a record exists in another table [Resolved]

[olamm2k]

Hi,

I have two tables:
- [Members] containing the fields MemID (a unique number), Surname and Forenames
- [RegMem] containing the fields SDate and MemID

The database holds information about my local hockey club. [RegMem] is a register of the members present on any date, so it contains a list of dates each linked to one MemID if the member was present on that date.
For example:
01/01/2005 - 007
01/01/2005 - 015
01/01/2005 - 126
01/01/2005 - 018
etc.

The program I am writing (in VB6, using ADO) has to output a list of the names of all the members and then a 'Y' or 'N' depending on whether they were present on that date.

At the moment, I am doing this by doing:
"SELECT Surname & Chr(44) & Chr(32) & Forenames AS FullName, MemID
FROM [Members];"
Then looping through each item in the created Recordset and doing (where dteSDate and intSDate are my passed arguments):
"SELECT MemID
FROM [RegMem]
WHERE SDate=#" & Format(dteSDate, "yyyy-mm-dd") & "# AND MemID=" & intMemID & ";"
Then testing if the RecordCount property of the Recordset is 0.

This is quite slow, as it involves creating one Recordset for the member list, then another one to test for each separate MemID. How could I improve this?

I thought of something along the lines of this:
"SELECT Surname & Chr(44) & Chr(32) & Forenames AS FullName
FROM Members
INNER JOIN RegMem
ON Members.MemID = RegMem.MemID
WHERE SDate = #" & Format(dteSDate, "yyyy-mm-dd") & "#;"
which returns the name of everyone present on a date, but I still need the full list of members.

Can this be done with something like RIGHT JOIN?

[PilgrimPete]

Yes, a LEFT or RIGHT OUTER JOIN can be used.
Something like this:

"SELECT Surname & Chr(44) & Chr(32) & Forenames AS FullName,
IIF(IsNull(RegMem.MemID), "N", "Y") AS Present
FROM Members
LEFT JOIN RegMem
 ON Members.MemID = RegMem.MemID
WHERE SDate = #" & Format(dteSDate, "yyyy-mm-dd") & "#;"

I'm assuming that you are using Access...

[olamm2k]

Yes, a LEFT or RIGHT OUTER JOIN can be used.
Something like this:

"SELECT Surname & Chr(44) & Chr(32) & Forenames AS FullName,
IIF(IsNull(RegMem.MemID), "N", "Y") AS Present
FROM Members
LEFT JOIN RegMem
 ON Members.MemID = RegMem.MemID
WHERE SDate = #" & Format(dteSDate, "yyyy-mm-dd") & "#;"

I'm assuming that you are using Access...

LEFT JOIN is the one that selects everything from the first table and just the records from the second table with the matching field specified, right?

What's the difference between an INNER and OUTER JOIN?

I am using access. I didn't know you could use functions like IIF and IsNull in statements like that. I'll give it a try - thanks.

[olamm2k]

I'm getting an error (expected end of statement) from the double quotes in the IIF statement (this is in VB):

strQuery = "SELECT Surname & Chr(44) & Chr(32) & Forenames " & _
          "AS FullName, IIF(IsNull(RegMem.MemID), [COLOR=Red]"N", "Y"[/COLOR]) " & _
          "AS Present From Members LEFT JOIN RegMem " & _
          "ON Members.MemID = RegMem.MemID WHERE SDate = #" & _
          Format(#7/4/2005#, "yyyy-mm-dd") & "#;"
        
        objRS.Open strQuery, objConn, adOpenStatic, adLockPessimistic

What's the proper syntax for this?

[PilgrimPete]

Yes, a LEFT JOIN takes all the records from the first table and the matching rows from the second. A RIGHT JOIN does the opposite - takes all the records from the second table and the matching rows from the first.
[OUTER is an optional Keyword in Access for LEFT and RIGHT.]
A FULL OUTER JOIN returns records from both tables regardless of matching... not all databases support this though.
There is an article on MSDN that might be of interest...
http://msdn.microsoft.com/library/de...qd_09_0zqr.asp

[PilgrimPete]

Double up your double quotes (or use CHR$(34)).

'doubling up double quotes
strQuery = "SELECT Surname & Chr(44) & Chr(32) & Forenames " & _
          "AS FullName, IIF(IsNull(RegMem.MemID), ""N"", ""Y"") " & _
          "AS Present From Members LEFT JOIN RegMem " & _
          "ON Members.MemID = RegMem.MemID WHERE SDate = #" & _
          Format(#7/4/2005#, "yyyy-mm-dd") & "#;"
                objRS.Open strQuery, objConn, adOpenStatic, adLockPessimistic

[olamm2k]

That works for displaying the character, but the query only returns the members who were present on that date, not those who were absent.