3

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 c_id of 50, I'd expect records 2 and 3 to come back. If I used c_id of 60, I'd expect records of 1 and 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).

1

if add indexes:

CREATE TABLE `table_b` (
  `S_ID` int(11) DEFAULT NULL,
  `C_ID` int(11) DEFAULT NULL,
  KEY `S_ID` (`S_ID`,`C_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `table_a` (
  `id` int(11) DEFAULT NULL,
  `S_ID` int(11) DEFAULT NULL,
  `C_ID` int(11) DEFAULT NULL,
  KEY `S_ID` (`S_ID`,`C_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

and little modify the query:

set @v_npref = 50;

SELECT ca.id, ca.S_ID, ca.C_ID
FROM TABLE_B AS clc 
JOIN TABLE_A AS ca ON (clc.S_ID = ca.S_ID AND clc.C_ID = @v_npref )
WHERE
ca.C_ID IS NOT NULL OR NOT EXISTS (SELECT id FROM TABLE_A ca2
                                 WHERE ca2.S_ID = ca.S_ID AND ca2.C_ID = @v_npref )

group by ca.S_ID;

we can compare plan of new and original queries, original: Original modified: Modified

this is just my variant, what better 2 FULL scan (as You can see total number of scanned rows bigger) or file sort at the final possible decide only on real data (depend from size of sorted data)

0

You Can Try This:

SELECT TABLE_B.S_ID,
       a.C_ID
       FROM ( SELECT TABLE_A.id as id,
                    TABLE_A.S_ID as S_ID,
                    TABLE_A.C_ID as C_ID
                   FROM TABLE_A
where C_ID IN ( SELECT MAX(TABLE_A.C_ID)
                                    FROM TABLE_A 
                           GROUP BY TABLE_A.S_ID ) OR C_ID IS NULL) as a 
RIGHT OUTER JOIN TABLE_B ON TABLE_B.S_ID =
                            a.S_ID AND a.C_ID = TABLE_B.C_ID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.