--- In firebird-support@yahoogroups.com, Uwe Oeder <uweo@c...> wrote:

> I try to run the following query but it always throws me an error
>
> SELECT WpIDSQ , W2.WpVBSQ , WpName , DescrData ,
> C.CityName || '(' || C.CityDiallingCode || ')' AS CityDialcode
> FROM Whitepage W2
> LEFT OUTER JOIN Description D ON (W2.WpIDSQ = D.WpVBSQ)
> LEFT OUTER JOIN SmallAddress S ON (W2.WpIDSQ = S.WpVBSQ)
> LEFT JOIN City C ON (W2.CityVBSQ = C.CityIDSQ)
> WHERE Upper(D.DescrData) LIKE '%PO BOX%'
> AND EXITS (
> SELECT WpIDSQ , W1.WpVBSQ , WpName , DescrData ,
> C.CityName || '(' || C.CityDiallingCode || ')' AS CityDialcode
> FROM Whitepage W1
> LEFT OUTER JOIN Description D ON (W1.WpIDSQ = D.WpVBSQ)
> LEFT OUTER JOIN SmallAddress S ON (W1.WpIDSQ = S.WpVBSQ)
> LEFT JOIN City C ON (W1.CityVBSQ = C.CityIDSQ)
> WHERE Upper(W1.WpName) LIKE '%UWE%'
> AND W1.WpIDSQ = W2.WpIDSQ
> );

Yuck, what kind of SQL is this? You have

a) in your WHERE clause you reference a table belonging to the right
side of a LEFT JOIN
b) use LEFT JOIN to reference a table neither with fields in your
output list nor further linked to another table
c) LEFT JOINs as part of your EXISTS
d) you use the same alias for multiple occurences of the same table
e) it can be confusing for readers that you some places use LEFT JOIN
and other LEFT OUTER JOIN, LEFT JOIN is simply an abbreviation for
LEFT OUTER JOIN.

Here's a guess at what you are trying to do:

SELECT W2.WpIDSQ , W2.WpVBSQ , W2.WpName , D.DescrData ,
C.CityName || '(' || C.CityDiallingCode || ')' AS CityDialcode
FROM Whitepage W2
LEFT JOIN Description D ON (W2.WpIDSQ = D.WpVBSQ AND
Upper(D.DescrData) CONTAINING 'PO BOX')
LEFT JOIN City C ON (W2.CityVBSQ = C.CityIDSQ)
WHERE EXISTS
(SELECT * FROM Whitepage W1
WHERE Upper(W1.WpName) CONTAINING 'UWE'
AND
W1.WpIDSQ = W2.WpIDSQ
)

If this is not what you want, I guess you are using LEFT [OUTER] JOINs
when what you really want is simply [INNER] JOINs.

HTH,
Set