Hi Gary!

To me, both seems meaningless, but should give the same result.

Why meaningless? Well, you do a LEFT join to sum a column in the right
table. To me, that indicates that you do not care about the records in
X that doesn't match any record in Y, hence what you are interested in
is an INNER JOIN (which the optimizer generally is better at handling)
, i.e.

SELECT SUM(Y.TOTAL) FROM TABLEONE X
JOIN TABLETWO Y ON X.LINK = Y.LINK AND Y.DELETED = 'N'
WHERE X.LINK = 1
(or your other alternative, both should work)

If your real query happens to include fields from both tables, then
your two queries will give different results:

SELECT X.FieldName, SUM(Y.TOTAL) FROM TABLEONE X
LEFT OUTER JOIN TABLETWO Y ON X.LINK = Y.LINK AND Y.DELETED = 'N'
WHERE X.LINK = 1

This one will include all records from X where LINK = 1 (regardless of
whether there is a matching Y with DELETED = 'N'), whereas

SELECT X.FieldName, SUM(Y.TOTAL) FROM TABLEONE X
LEFT OUTER JOIN TABLETWO Y ON X.LINK = Y.LINK
WHERE X.LINK = 1 AND Y.DELETED = 'N'

will only include those tuples where Y.DELETED = 'N', i.e. equal the
inner join I wrote above (I think - there is plenty of people on this
list that will tell me if I miss the mark).

HTHANC (Hope this helps and not confuses),
Set (Svein Erling Tysvær)

--- In firebird-support@yahoogroups.com, "Alan McDonald" wrote:
> > Will the following two queries give the same results? Is there a
> > beneifit to
> > doing it either way?
> >
> > SELECT SUM(Y.TOTAL) FROM TABLEONE X
> > LEFT OUTER JOIN TABLETWO Y ON X.LINK = Y.LINK AND Y.DELETED = 'N'
> > WHERE X.LINK = 1
> >
> > SELECT SUM(Y.TOTAL) FROM TABLEONE X
> > LEFT OUTER JOIN TABLETWO Y ON X.LINK = Y.LINK
> > WHERE X.LINK = 1 AND Y.DELETED = 'N'
> >
> > TIA
> > Gary
>
> does the first one work at all? I would never have thought it to be
> valid query syntax. the second would be my natural way to do it.
> Alan