Testing if a record exists in another table [Resolved]

[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?