topic Re: Problem with left outer join in Application Development and Automation Discussions

Problem with left outer join

Former Member — Wed, 21 Mar 2007 12:32:36 GMT

We defined an InfoSet with only two tables. Between these tables there is a left outer join (SQ02).

Then we defined an InfoSet-Query (SQ01) with selections on both tables.

The result should be a table with all data which can be find in the right table.

- But we only get the data, if there is a suitable value in the right table.

- but this should be the result of an inner join.

Who can help?

Re: Problem with left outer join

Former Member — Wed, 21 Mar 2007 12:53:57 GMT

Yes that should work...

Here are some examples... they may help you...

--match cities to countries

SELECT CITIES.COUNTRY, REGION

FROM Countries

LEFT OUTER JOIN Cities

ON CITY_ID=CITY_ID

WHERE REGION = 'Asia';

-- use the synonymous syntax, RIGHT JOIN, to achieve exactly

-- the same results as in the example above

SELECT COUNTRIES.COUNTRY, REGION

FROM Countries

LEFT JOIN Cities

ON CITY_ID=CITY_ID;

-- Join the EMPLOYEE and DEPARTMENT tables,

-- select the employee number (EMPNO),

-- employee surname (LASTNAME),

-- department number (WORKDEPT in the EMPLOYEE table

-- and DEPTNO in the DEPARTMENT table)

-- and department name (DEPTNAME)

-- of all employees who were born (BIRTHDATE) earlier than 1930

SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME

FROM SAMP.EMPLOYEE LEFT OUTER JOIN SAMP.DEPARTMENT

ON WORKDEPT = DEPTNO

AND YEAR(BIRTHDATE) < 1930;

-- List every department with the employee number and

-- last name of the manager,

-- including departments without a manager

SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME

FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE

ON MGRNO = EMPNO;

Re: Problem with left outer join

Former Member — Wed, 21 Mar 2007 12:54:36 GMT

A LEFT OUTER JOIN is one of the JOIN operations that allow you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.