0

So I have a complicated INSERT query which puts information into a relational table based on information pulled from another table and with given variables.

Here's the query:

INSERT INTO relational (class_id, teacher_id, student_id)
VALUES (
    (SELECT `id`,
        (`name` = "John Smith") as exact_score,
        (`name` RLIKE "[[:<:]]John Smith[[:>:]]") as reg_score,
        (MATCH (`name`) AGAINST ("John Smith")) as match_score
    FROM `teachers`
    WHERE (`title` RLIKE "[[:<:]]John Smith[[:>:]]" || MATCH (`title`) AGAINST ("John Smith"))
    ORDER BY
        CASE WHEN exact_score = 1 THEN exact_score END DESC,
        CASE WHEN reg_score > 1 THEN reg_score END DESC,
        CASE WHEN reg_score = 0 AND exact_score = 0 THEN match_score END DESC),
1, 1);

The regex is required because the name might be Mr. John Smith or John Smith PH.d, and the match is required because the name might be jon smith When the stored name is actually just John Smith.

The select query yields the results I am looking for 90% of the time, but the only real column that I need from it is id. The problem I'm running into is with the INSERT's expected column count vs. the column count that is returned by the query.

Right now the INSERT basically looks like this:

INSERT INTO relational (class_id, teacher_id, student_id) VALUES ({{returned id (desired)}}, {{exact score (undesired)}}, {{reg score (undesired)}}, {{match score (undesired)}}, 1, 1);

The last 1, 1 is provided by variables.

My question is, is there a way to select columns for the sake of ORDER BY, but then to omit them from the results?

1 Answer 1

0

There is no need to use VALUES; simplify the insert to

INSERT INTO t (class_id, teacher_id, student_id)
    SELECT id, 1, 1 FROM ...

To get rid of extra columns, make an extra level of SELECT:

SELECT a, b
    FROM (
        SELECT a, extra, b FROM ...
         ) AS x

But, you probably won't have any extra columns if you do

ORDER BY
    IF (`name` = "John Smith", 2, 
        IF (`name` RLIKE "[[:<:]]John Smith[[:>:]]", 1,
            MATCH (`name`) AGAINST ("John Smith")
       )   )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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