Re: SQR-USERS Digest - 12 Mar 2000 to 13 Mar 2000 (#2000-65)

[Bruce Mitchell <mitchellb@CYBERUS.CA>]

I use a Union with good success rather than an outer join.

BEGIN-SELECT
A.FIRSTFIELD
A.SECONDFIELD
FROM TABLE A
WHERE [$WHERECLAUSE]

UNION

SELECT
B.FIRSTFIELD
B.SECONDFIELD
FROM TABLE B
WHERE [$WHERECLAUSE2]
END-SELECT

Remember, both sides of the Union must return the same number and type of
fields in the same order.

Works really well.

Another approach for your example that will work with SQLServer (you didn't
indicate what platform you're on) is to use a select statement as a column
like this:

BEGIN-SELECT
A.FIRSTFIELD
A.SECONDFIELD
(SELECT B.FIRSTFIELD FROM TABLE2 B WHERE B.KEY = A.KEY AND ... ) &THIRDFIELD
FROM TABLE1 A
WHERE ...
END-SELECT

Bruce

> -----Original Message-----
>
> Date:    Mon, 13 Mar 2000 14:14:24 EST
> From:    NEDOLPH@AOL.COM
> Subject: Left Join
>
> In PeopleSoft SQR Running on DB2  How can the LEFT JOIN syntax be made to
> work in the Select?
> As a trivial example...
>
> Table A Fields: EMPLID and Name
> Table B fields:EMPLID and Phone
> I want to return ALL records in table A with the phone numbers
> from table B
> where they exist and null or ' ' where there is no phone number.
>
> my JOIN efforts either won't run at all or only return rows where
> there is a
> phone.
> I can do this with 2 nested select statements but it takes forever to run.
>
> Are the words "LEFT JOIN" legal in SQR and if so, what commas,
> ()'s etc are
> necessary to get them to work.
>
> Many Thanks
> N. Dolph
>