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.

[PilgrimPete]

Oh, I get it. I wasn't paying attention!

You'll be needing a subquery then...

strQuery = "SELECT Surname & Chr(44) & Chr(32) & Forenames " & _
          "AS FullName, IIF(IsNull(RegMem.MemID), ""N"", ""Y"") " & _
          "AS Present From Members LEFT JOIN (SELECT MemID FROM RegMem WHERE SDate = #" & Format(#7/4/2005#, "yyyy-mm-dd") & "#) RegMem " & _
          "ON Members.MemID = RegMem.MemID

I doubt if that is syntactically spot on, but you get the idea?

[olamm2k]

It works, but I'm not quite clear on the part between the 'LEFT JOIN' and 'ON' - the subquery followed by a table name. How does that work?

[PilgrimPete]

(SELECT MemID FROM RegMem WHERE SDate = #" & Format(#7/4/2005#, "yyyy-mm-dd") & "#) AS AnyNameYouLike "

The subquery is effectively like an inline query, I just happened to have aliased it to the same name as the table it came from in the previous post (and I was lazy and skipped the 'AS').
You would get the same effect if you created a saved query in access with that SQL, and JOINed on that instead. Does that make it any clearer?

[olamm2k]

The missing 'AS' did throw me.

I've now got it as:

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

But why is the subquery needed?

[PilgrimPete]

Because you don't want to limit the whole result set, just the subset of members present on that date.

The original query I posted had the following logic:
Return rows for all members, joining all records in RegMem, marking those not present with an 'N', BUT that have a row in RegMem with an SDate of xyz. This is contradictory rubbish.
The revised query has this logic:
Return rows for all members, joining ONLY those rows in RegMem with an SDate of xyz, marking those not present with an 'N'.

This is surprisingly hard to explain!
Hope I got close though

[olamm2k]

It's an odd concept to get your head around, but I think the first query would be the same as doing:

"SELECT Surname & Chr(44) & Chr(32) & Forenames " & _
  "AS FullName, IIF(IsNull(RegMem.MemID), ""N"", ""Y"") " & _
  "AS Present FROM Members, RegMem WHERE SDate = #" & _
  Format("2005-04-07", "yyyy-mm-dd") & "# AND " & _
  "Members.MemID = RegMem.MemID;"

Thanks for your help.