SUB-SELECT static data not filtered in joins [CORE1808] #2236
Comments
Modified by: Tim Kelly (m00bh000)description: SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN Unless I am mistaken the above query should return a row for every item in TABLE_A with DID_JOIN returned as 'NOT_JOINED' for every item in TABLE_A which has a corresponding entry in TABLE_B and 'JOINED' for every entry in TABLE_A that does have a corresponding entry in TABLE_B. But when you run the query all data is returned as 'JOINED', it appears that the static text 'JOINED' slips through the left-join. Doing this kinda fixes it (but then you get 'JOINED' || ID, when really you just want 'JOINED'): SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN => SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN Unless I am mistaken the above query should return a row for every item in TABLE_A with DID_JOIN returned as 'NOT_JOINED' for every item in TABLE_A which has no corresponding ID in TABLE_B and 'JOINED' for every entry in TABLE_A that does have a corresponding ID in TABLE_B. But when you run the query all data is returned as 'JOINED', it appears that the static text 'JOINED' slips through the left-join. Doing this kinda fixes it (but then you get 'JOINED' || ID, when really you just want 'JOINED'): SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN |
Modified by: Tim Kelly (m00bh000)description: SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN Unless I am mistaken the above query should return a row for every item in TABLE_A with DID_JOIN returned as 'NOT_JOINED' for every item in TABLE_A which has no corresponding ID in TABLE_B and 'JOINED' for every entry in TABLE_A that does have a corresponding ID in TABLE_B. But when you run the query all data is returned as 'JOINED', it appears that the static text 'JOINED' slips through the left-join. Doing this kinda fixes it (but then you get 'JOINED' || ID, when really you just want 'JOINED'): SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN => SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN Unless I am mistaken the above query should return a row for every item in TABLE_A with DID_JOIN returned as 'NOT_JOINED' for every item in TABLE_A which has no corresponding ID in TABLE_B and 'JOINED' for every entry in TABLE_A that does have a corresponding ID in TABLE_B. But when you run the query all data is returned as 'JOINED', it appears that the static text 'JOINED' slips through the left-join. I have found a work around, by including concating data with the static text and then running substring on it! >> SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN |
|
Commented by: @asfernandes This seems to be the same problem of CORE1246. |
Modified by: Tim Kelly (m00bh000)description: SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN Unless I am mistaken the above query should return a row for every item in TABLE_A with DID_JOIN returned as 'NOT_JOINED' for every item in TABLE_A which has no corresponding ID in TABLE_B and 'JOINED' for every entry in TABLE_A that does have a corresponding ID in TABLE_B. But when you run the query all data is returned as 'JOINED', it appears that the static text 'JOINED' slips through the left-join. I have found a work around, by including concating data with the static text and then running substring on it! >> SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN => ----------- CREATE TABLE TABLE_A ( CREATE TABLE TABLE_B ( INSERT INTO TABLE_B VALUES (2); INSERT INTO TABLE_A VALUES (1); ---------- SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN Returns: ID DID_JOIN When it should return: ID DID_JOIN Unless I am mistaken the above query should return a row for every item in TABLE_A with DID_JOIN returned as 'NOT_JOINED' for every item in TABLE_A which has no corresponding ID in TABLE_B and 'JOINED' for every entry in TABLE_A that does have a corresponding ID in TABLE_B. But when you run the query all data is returned as 'JOINED', it appears that the static text 'JOINED' slips through the left-join. I have found a work around, by including concating data with the static text and then running substring on it! >> SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN |
Modified by: Tim Kelly (m00bh000)description: ----------- CREATE TABLE TABLE_A ( CREATE TABLE TABLE_B ( INSERT INTO TABLE_B VALUES (2); INSERT INTO TABLE_A VALUES (1); ---------- SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN Returns: ID DID_JOIN When it should return: ID DID_JOIN Unless I am mistaken the above query should return a row for every item in TABLE_A with DID_JOIN returned as 'NOT_JOINED' for every item in TABLE_A which has no corresponding ID in TABLE_B and 'JOINED' for every entry in TABLE_A that does have a corresponding ID in TABLE_B. But when you run the query all data is returned as 'JOINED', it appears that the static text 'JOINED' slips through the left-join. I have found a work around, by including concating data with the static text and then running substring on it! >> SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN => ----------- CREATE TABLE TABLE_A ( CREATE TABLE TABLE_B ( INSERT INTO TABLE_B VALUES (2); INSERT INTO TABLE_A VALUES (1); ---------- SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN Returns: ID DID_JOIN When it should return: ID DID_JOIN Unless I am mistaken the above query should return a row for every item in TABLE_A with DID_JOIN returned as 'NOT_JOINED' for every item in TABLE_A which has no corresponding ID in TABLE_B and 'JOINED' for every entry in TABLE_A that does have a corresponding ID in TABLE_B. But when you run the query all data is returned as 'JOINED', it appears that the static text 'JOINED' slips through the left-join. I have found a work around, by including concating data with the static text and then running substring on it! >> SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN |
|
Commented by: Tim Kelly (m00bh000) Any1 fancy taking on this one? |
Modified by: @asfernandes |
|
Commented by: @asfernandes Duplicates CORE1246. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pavel-zotovQA Status: No test |
Submitted by: Tim Kelly (m00bh000)
Duplicates CORE1246
-----------
CREATE TABLE TABLE_A (
ID INTEGER);
CREATE TABLE TABLE_B (
ID INTEGER);
INSERT INTO TABLE_B VALUES (2);
INSERT INTO TABLE_B VALUES (4);
INSERT INTO TABLE_B VALUES (6);
INSERT INTO TABLE_A VALUES (1);
INSERT INTO TABLE_A VALUES (2);
INSERT INTO TABLE_A VALUES (3);
----------
SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN
FROM
TABLE_A
LEFT JOIN
(SELECT ID, 'JOINED' AS DID_JOIN FROM TABLE_B) TABLE_B_VIEW ON TABLE_B_VIEW.ID = TABLE_A.ID
Returns:
ID DID_JOIN
-------------------------------------
1 JOINED
2 JOINED
3 JOINED
When it should return:
ID DID_JOIN
-------------------------------------
1 NOT JOINED
2 JOINED
3 NOT JOINED
Unless I am mistaken the above query should return a row for every item in TABLE_A with DID_JOIN returned as 'NOT_JOINED' for every item in TABLE_A which has no corresponding ID in TABLE_B and 'JOINED' for every entry in TABLE_A that does have a corresponding ID in TABLE_B.
But when you run the query all data is returned as 'JOINED', it appears that the static text 'JOINED' slips through the left-join.
I have found a work around, by including concating data with the static text and then running substring on it!
>>
SELECT TABLE_A.ID, COALESCE(TABLE_B_VIEW.DID_JOIN, 'NOT JOINED') AS DID_JOIN
FROM
TABLE_A
LEFT JOIN
(SELECT ID, SUBSTRING('JOINED' || ID FROM 1 FOR 6) AS DID_JOIN FROM TABLE_B) TABLE_B_VIEW ON TABLE_B_VIEW.ID = TABLE_A.ID
The text was updated successfully, but these errors were encountered: