1

I need to run select, but only if some condition is met, but I also want to execute the condition only once, so it there is less overhead. I tried something as this:

SELECT
    IF((SELECT "1" FROM `lchat`.`chats` WHERE 3 IN (`user1`, `user2`)),
       (SELECT * FROM `lchat`.`messages`
          WHERE `chat`=10 ORDER BY `ct` desc LIMIT 50
       ), null);

but it gives me a 1241 error. I also tried to use the if statement, but in mysql it works only in procedures (at least from what I tried) and they have the same problem when I want to output the final data. edit: they don't because I don't have to return the value from the procedure, but I can just read the output of the select inside of it.

3
  • What results do you want? Perhaps between 0 and 50 rows with the columns of messages?
    – Rick James
    Oct 16, 2021 at 15:48
  • @RickJames, yep, but only if the user (I tested it with user with id 3) is in the channel. Oct 16, 2021 at 16:11
  • Please describe how chats, channels, messages, and users are related.
    – Rick James
    Oct 17, 2021 at 5:05

2 Answers 2

1

That's not how (pure) SQL works. SQL is a declarative language - you describe the results you want (the form and conditions you want rows to conform to) and the server thinks of a way to do that and give you the results. When you want to tell a computer to "do THIS and then, based on the results maybe do THAT too", that's "procedural" programming and needs a procedural language. That is impossible to write as an SQL query.

But most DB system have some procedural extension over basic SQL - you can use a stored procedure or function in MySQL to do what you describe.

In the stored program you can use a "real" IF construct (and some variables etc) to do what you need. It will not be a single query, but possibly multiple, some of them executed always, some conditionally based on the results of previous ones.

The IF() you try to use here is not a flow control statement, it is an expression. Even if you wrote it compatible with SQL, all the parts would be evaluated always and only then the results would be constructed. (Well, the optimizer can do some smart things if it knows it can skip something, but this is not the case imho)

1

Perhaps you are looking "messages" where "user" number "3" is either the sender ("user1") or the recipient ("user2")?

Ponder this:

SELECT m.*                -- deliver all columns of each picked message
    FROM chats AS c       
    JOIN messages AS m    -- 2 tables are needed
          ON m.chat_id = c.chat_id  -- how those tables are related
    WHERE m.chat_id = 10    -- looking only at thread #10
      AND (               -- parens are needed when mixing AND and OR
               c.sender = 3
            OR c.recipient = 3
          )               -- user #3 is involved on either side
    ORDER BY m.timestamp DESC  -- deliver newest first
    LIMIT 50              -- no more than 50 messages

But that might be more complicated than necessary? What is the purpose of the table chat? Are the sender and recipient in both tables? Maybe you can search only messages? Please provide SHOW CREATE TABLE so that we can discuss specifics of your schema.

(Meanwhile jkavalik gives you good info about how SQL works and does not work.)

3
  • The table messages contains just the messages (their author, channel, text, .....) and the table chats contains the channels, so all users, that are in them, .... . And I need to run the select only if the user has an access to the channel. I know about this solution, but it seems as an useless waste of resources to check it on each line, when I can check it just once (because both, the user and the channel are same in the whole query). I tried the procedures again as jkavalik mentioned and it works (first time I forgot, that the select inside of it also returns it's result to my client). Oct 16, 2021 at 16:23
  • @doc.Chocholoušek - The Optimizer will get rid of redundant steps if it can tell that they are redundant. I don't understand the concept of "channel", so I can't fix my suggested query. Is there another table called Channel? Is your chat=10 really channel_id = 10? (Etc.)
    – Rick James
    Oct 16, 2021 at 16:52
  • Sorry, for the confusion, but I use chat and channel for the same thing and I have there a separated table for info about the whole chat, that contains all users, that have an access to the chat and a table for info about the messages, that I need to query. Oct 16, 2021 at 20:52

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.