Watch, Follow, &
Connect with Us
Public Report
Report From: InterBase/Server    [ Add a report in this area ]  
Report #:  129897   Status: Reported
Left joins excludes results
Project:  InterBase Build #:  11.0.4.0
Version:    11.0 Submitted By:   Johnny Franzen
Report Type:  Basic functionality failure Date Reported:  1/27/2015 6:26:20 AM
Severity:    Serious / Highly visible problem Last Updated: 1/27/2015 4:39:48 PM
Platform:    All platforms Internal Tracking #:  
Resolution: None  Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
Using a left join with an extra boolean criteria excludes rows from result if there are more than one of them.
Steps to Reproduce:
CREATE TABLE STUDENTS (ID INTEGER, NAME VARCHAR (25))

CREATE TABLE PROFESSORS (ID INTEGER, NAME VARCHAR (25), TITLE VARCHAR (10))

CREATE TABLE LECTURES (LECTURER_ID INTEGER, LECTURER_TYPE CHAR (1), LECTURE_NAME VARCHAR (30))

INSERT INTO STUDENTS (ID, NAME) VALUES (1, 'Mina Harker');

INSERT INTO STUDENTS (ID, NAME) VALUES (2, 'Jonathan Harker');

INSERT INTO STUDENTS (ID, NAME) VALUES (3, 'Arthur Holmwood');

INSERT INTO STUDENTS (ID, NAME) VALUES (4, 'Lucy Westenra');

INSERT INTO PROFESSORS (ID, TITLE, NAME) VALUES (1, 'Professor', 'Abraham Van Helsing');

INSERT INTO PROFESSORS (ID, TITLE, NAME) VALUES (2, 'Doctor', 'John Seward');

INSERT INTO PROFESSORS (ID, TITLE, NAME) VALUES (3, 'Lord', 'Arthur Holmwood');

INSERT INTO LECTURES (LECTURER_ID, LECTURER_TYPE, LECTURE_NAME) VALUES (1, 'P', 'Vampire hunting');

INSERT INTO LECTURES (LECTURER_ID, LECTURER_TYPE, LECTURE_NAME) VALUES (2, 'S', 'Transsylvanian architecture');

INSERT INTO LECTURES (LECTURER_ID, LECTURER_TYPE, LECTURE_NAME) VALUES (2, 'P', 'Blood transfusion');

INSERT INTO LECTURES (LECTURER_ID, LECTURER_TYPE, LECTURE_NAME) VALUES (4, 'S', 'Virtues of garlic')

==============

This query works as excpected:
SELECT
    L.*,
    S.*
FROM
    LECTURES L
LEFT JOIN
    STUDENTS S ON L.LECTURER_ID = S.ID AND L.LECTURER_TYPE = 'S'

I see all lectures and student info only for rows which lecturer_type is S.

==============

This query doesn't work as expected:
SELECT
    L.*,
    S.*,
    P.*
FROM
    LECTURES L
LEFT JOIN
    STUDENTS S ON L.LECTURER_ID = S.ID AND L.LECTURER_TYPE = 'S'
LEFT JOIN
    PROFESSORS P ON L.LECTURER_ID = P.ID AND L.LECTURER_TYPE = 'P'

The result is only two rows. Only the two rows with lecturer_type P is shown. If the order of the left joins are reversed, only the S rows are shown. I naturally expect that all lectures are shown, and the correct tables are joined to the corresponding row.
Workarounds
I have found no workaround.
Attachment
None
Comments

Johnny Franzen at 1/27/2015 6:31:28 AM -
Basically, this is what I am trying to achieve: https://stackoverflow.com/questions/17551326/conditional-sql-join-based-on-column-contents

The problem is better described there too.

Server Response from: ETNACODE01