I'm not sure if this is the most appropriate place for this question, but I thought I'd give it a shot.
The scenario is wanting to retrieve records from a table where a column is null, but if an exact match is found, preference that row instead.
Here's the table structure:
CREATE TABLE TABLE_A ( id INT, S_ID INT, C_ID INT ); CREATE TABLE TABLE_B ( S_ID INT, C_ID INT ); INSERT INTO TABLE_A VALUES(1, 10, NULL); INSERT INTO TABLE_A VALUES(2, 10, 50); INSERT INTO TABLE_A VALUES(3, 12, NULL); INSERT INTO TABLE_B VALUES(10, 50); INSERT INTO TABLE_B VALUES(10, 60); INSERT INTO TABLE_B VALUES(12, 50); INSERT INTO TABLE_B VALUES(12, 60);
When using a
50, I'd expect records
3 to come back. If I used
60, I'd expect records of
3 to come back.
At the moment, I have this query to solve the issue, but I'm positive there is a better way to do it, so I'm asking for ideas :)
SELECT ca.* FROM TABLE_B AS clc JOIN TABLE_A AS ca ON (clc.S_ID = ca.S_ID AND clc.C_ID = 50) WHERE ( ca.C_ID IS NULL AND ca.S_ID NOT IN ( SELECT S_ID FROM TABLE_A WHERE C_ID = clc.C_ID ) ) OR ( ca.C_ID = clc.C_ID );
Unfortunately, the tables have no indexes, and no foreign keys. The table structure cannot be changed (legacy).