Outer Join Question 1

[bholm]

I need to know how to do a SQL query in Access that does an outer join with another table...but here's the problem:
The joined table needs to be qualified by date...i.e. I want the results set to show ALL records qualified in the main select statement (all locations by City found in 'British Columbia') BUT I only want to join with those records in the Events table that have an eventDate > today.
How do I do that?

SELECT DISTINCTROW Locations.City, Locations.StateProv, Locations.Contact, Locations.Phone1, Events.eventLocID, Events.EventName, Events.EventDate, Events.EventDetails
FROM Locations LEFT JOIN Events ON Locations.ID = Events.eventLocID
WHERE Locations.StateProv = 'British Columbia'
GROUP BY Locations.StateProv, Locations.City, Locations.Contact, Locations.Phone1, Events.EventLocID, Events.EventName, Events.EventDate, Events.EventDetails ORDER BY City, EventDate

I am used to Oracle SQL where you can use the IN operator (?) such as WHERE id IN ( SELECT eventLocID FROM events WHERE eventDate > #11/11/02#)
But when I tried that below, it gives an syntax error so I assume Access doesn't understand IN.

SELECT DISTINCTROW Locations.City, Locations.StateProv, Locations.Contact, Locations.Phone1, Events.eventLocID, Events.EventName, Events.EventDate, Events.EventDetails FROM Locations LEFT JOIN Events ON Locations.ID IN ( SELECT EventLocID FROM Events WHERE EventDate > #11/11/02# ) WHERE Locations.StateProv = 'British Columbia' GROUP BY Locations.StateProv, Locations.City, Locations.Contact, Locations.Phone1, Events.EventLocID, Events.EventName, Events.EventDate, Events.EventDetails ORDER BY City, EventDate

 

[cmmrfrds]

The IN works in Access, but literals must be surrounded by double quotes - different than ANSI SQL. Unless the query is being built in ADO then the single quote is okay.

"British Columbia"

 

[bholm]

The error message isn't complaining about single or double quotes around British Columbia, it's complaining about the IN subquery...it states there's an error in 'Locations.ID IN ( SELECT EventLocID FROM Events WHERE EventDate > #11/11/02# ) WHERE Locations.StateProv = "British Columbia" GROUP B'

I tried putting double-quotes just in case but it didn't help the error to go away.

Any other ideas out there? Is my approach wrong? This should be a do-able query type, just don't know the syntax for Access.

 

[r937]

yeah, you cannot do the composite left outer in access

that is, in access 97 (i have nothing later to test with)

it complains here --

  from Locations 
left outer
  join Events 
    on Locations.ID = Events.eventLocID
   and EventDate > #11/11/02#

which is perfectly fine (except for the hashed date) in any other database

i tried putting the date qualification into a query and then left joining the query, but no dice

rudy

 

[cmmrfrds]

This seems to work in Access 2000.

left outer
join Events
on Locations.ID = Events.eventLocID
where EventDate > #11/11/02#

Should be able AND the where clause

 

[bholm]

This doesn't give an error but it doesn't give the results set I need. It disqualifies locations that don't have any events and disqualifies those that have events that only have events older than today.

I need all locations (with or without events) but only join with events that are future.

Thanks for those who have made suggestions...keep 'em coming.

 

[r937]

this is a "brute force" method but it should work

back in the old days, when databases did not yet support the outer join syntax, we had to achieve the same results a different way

an outer join is equivalent to an inner join, to pick up rows where a match exists, combined with a "not exists" query, to pick up rows from the left table which don't have a match

thus, the left outer join query you want to run is equivalent to the following

[tt]select Locations.City
, Locations.StateProv
, Locations.Contact
, Locations.Phone1
, Events.eventLocID
, Events.EventName
, Events.EventDate
, Events.EventDetails
from Locations
inner
join Events
on Locations.ID = EventLocID
where Locations.StateProv = 'British Columbia'
and EventDate > #11/11/02#
group
by Locations.StateProv
, Locations.City
, Locations.Contact
, Locations.Phone1
, Events.EventLocID
, Events.EventName
, Events.EventDate
, Events.EventDetails
union all
select Locations.City
, Locations.StateProv
, Locations.Contact
, Locations.Phone1
, null
, null
, null
, null
from Locations
where Locations.StateProv = 'British Columbia'
and not exists
( select 1
from Events
where EventLocID = Locations.ID
and EventDate > #11/11/02# )
group
by Locations.StateProv
, Locations.City
, Locations.Contact
, Locations.Phone1
order
by City
, EventDate[/tt]

note that i have left your GROUP BY clauses in there, because you had them in your original query, although i seriously doubt that they are required, since in the first of the two queries, by grouping on the event it seems to suggest there are multiple occurrences of the event ID (which i highly doubt) and in the second query, ditto for the location ID --so please remove the GROUP BY clauses, okay?

rudy

 

[bholm]

With the addition of "Events." to a couple column names in the Join portion of the query....IT WORKS!!!

THANKS!!!

I should have thought of the UNION operator but didn't.

I am curious why UNION ALL is done instead of just UNION since UNION ALL includes duplicates?

 

[r937]

>> THANKS!!!

my pleasure

i should have recalled that the first time, when i tested your outer syntax and had it barf on me too

>> I am curious why UNION ALL is done instead of
>> just UNION since UNION ALL includes duplicates?

i am so glad you asked that

i've wanted to explain this in detail for a long time

(i may still steal my own words and post it as an article on my site some day)

UNION removes duplicate rows, while UNION ALL doesn't even check for them, it just gives you whatever rows the individual subqueries returned

but what are duplicate rows? they would have to be the same in every column!

the significance of this will be apparent in a moment

imagine two subqueries: query1 and query2

if query1 produced duplicate rows by itself, it's reasonable to expect that the person coding it would have used DISTINCT, right? assuming that, as in most real world cases, completely duplicate rows would not ordinarily be produced

in other words, before you ever write a UNION, you'll probably write query1 by itself, and if it produced duplicate rows, you'd notice, and take appropriate action

okay, same with query2, if it produced duplicate rows by itself, you'd use DISTINCT to remove the duplicate rows

so now consider query1 UNION query2

if you assume that neither query produces duplicate rows by itself, then the only way that duplicate rows can possibly happen is if one of them comes from query1 and the other one from query2

with me so far?

okay, now consider what the database must do in order to detect completely duplicate rows

it has to sort the combined result set on every column, and then compare rows, two at a time!

whoa!

furthermore, this sort will very often be nowhere near as efficient as, say, originally retrieving rows via indexes, because we are talking here about two intermediate result sets that have already been retrieved, which now must be sorted together so that pairs of rows can be compared on every column to detect duplicates

whew, almost done

so it stands to reason that you would never want to force this sort on all columns of the merged record sets, if you knew ahead of time that there's no way you could have got duplicate rows out of query1 and query2

conclusion? for the sake of efficiency, avoid the sort if it's not necessary

(incidentally, it is for this very same reason that you should not toss DISTINCT into individual queries indiscriminately)

always code UNION ALL, and only code UNION when you know there will be duplicate rows

now look back at your example: an inner join query, where columns from the second table come from matched rows, combined in a UNION with a "not exists" query, where the columns from the second table are actually nulls in placeholder positions

no way those can be equal, right?

;o)

 

[bholm]

This makes a lot of sense to me. Thanks for the lengthy and much appreciated explanation!

I forgot to mention last time that I think you are right about the GROUP BY claus as well so I removed them. I'm using this query in Cold Fusion within a <CFOUTPUT GROUP=&quot;&quot;> type of tag where you specify a gouping...I tend fall into the trap that grouping the results set with that tag requires using the GROUP BY clause but it doesn't.

This has been a very helpful discussion. I found your use of select 1 interesting to, and the filling of columns with NULL in one of your UNION ALL selects. Clever!

 

[r937]

>> ... the trap that grouping the results set
>> with [the GROUP= parameter] requires using
>> the GROUP BY clause but it doesn't.

that's right, it doesn't

in fact, you can have cases where the GROUP= coldfusion parameter operates on a different sequence than the result set data as determined by the ORDER BY

i have an example, but i would have to write it up, eh

 

[webmigit]

Give the man a round of applause, or rather a purple star.. Did I help?
Vote!