'Right outer join with multiple where conditions

I'm having some issues returning any results from my SQL query. I'm pretty sure it's got something to do with the WHERE condition.

I want only certain RULE_ID's to be returned. I identify the RULE_ID by it's RULE_NAME in TABLE1. I then join TABLE1 and TABLE2 and say if the column DATE for the RULE_ID is equal to 05-MAR-20 and the column COLUMN3 is not null, return the RULE_ID.

At the moment I'm not getting any results with this query and I'm not entirely sure why. Any help would be much appreciated!

EDIT: COLUMN3 is in TABLE2 but not in TABLE1. I'm not getting an error, just no results are showing when I think I should be getting some. If I remove the AND (TABLE2.DATE = '05-MAR-20') I get several results. Not sure why this is...

sql_query = """
            SELECT TABLE1.RULE_ID, TABLE2.RULE_ID
            FROM TABLE1
            RIGHT OUTER JOIN TABLE2
            ON (TABLE1.RULE_ID = TABLE2.RULE_ID)
            WHERE (TABLE1.RULE_NAME = 'TEST1' OR TABLE1.RULE_NAME = 'TEST2')  
            AND (TABLE2.DATE = '05-MAR-20')
            AND COLUMN3 IS NOT NULL
            """
javapythonsqljython


Solution 1:[1]

Use LEFT JOIN. Much easier to follow the logic ("keep all rows in the first table". Then move appropriate filtering to the ON clause):

SELECT TABLE1.RULE_ID, TABLE2.RULE_ID
FROM TABLE2 LEFT JOIN
     TABLE1
      ON TABLE1.RULE_ID = TABLE2.RULE_ID AND
         TABLE1.RULE_NAME IN ('TEST1', 'TEST2')   
WHERE TABLE2.DATE = DATE '2020-03-05' AND TABLE2.COLUMN3 IS NOT NULL

Solution 2:[2]

I've found the issue - the dates are timestamped. In the dB, the date just says, DD-MON-YY, but when I return the dates I also have DD-MON-YY, HH:MM:SS.0. Just have to find a way now to get rid of the timestamp and just have the date.

Solution 3:[3]

Not sure if you've seen this post yet (or are even still trying to solve this issue lol) but: how to remove time from datetime

If I understand the post above correctly, there may be different way to call the date part of the date-time field depending on the format of the field and the interface you are using to interact with it.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gordon Linoff
Solution 2 says
Solution 3 Trevor Viscardi

Related Questions