0

I made this query:

    SELECT 
        COALESCE(
                 (SELECT * 
                  FROM JournalEntryTitleAndContent jetc 
                  WHERE jetc.language_id = 2 
                        AND jetc.journalentry_id = 1 LIMIT 1),
                 (SELECT * 
                  FROM JournalEntryTitleAndContent jetc 
                  WHERE jetc.language_id = 1 
                        AND jetc.journalentry_id = 1 LIMIT 1),
                 (SELECT * 
                  FROM JournalEntryTitleAndContent jetc 
                  WHERE jetc.journalentry_id = 1 LIMIT 1)
                 )

Its purpose is to check if there is a row present with language_id 2 (thats the preferred language). If that is not present then the coalesce should try to find the row with the fallbacklanguage with id 1, if that returns null as well then it should return the first row it can find where journalentry_id = 1.

When I execute this statement in workbench i get Error Code 1241: Operand should contain 1 column(s)

Can i not return a row as a single value from coalesce? If not what should i use instead then? Should i use CASE or IFNULL() instead?

Thank you

Improve this question
0

1 Answer 1

Reset to default
2 
SELECT * 
FROM JournalEntryTitleAndContent jetc 
WHERE jetc.journalentry_id = 1 
ORDER BY FIND_IN_SET(jetc.language_id, '1,2') DESC
LIMIT 1
Improve this answer
5
  • What an interesting query Akina! I've never heard of the FIND_IN_SET function before. I do have a question about it. The FIND_IN_SET method seems to return that position of the language_id in the set '1,2' and it will return 0 if the language_id cannot be found. Wouldn't that throw an error? Also, I don't understand how you can use the return value (which is a number) for the ORDER BY argument. Could you explain what is happening in your query a bit further please? I'm still a bit of a novice when it comes to SQL.
    – Maurice
    Mar 3, 2020 at 12:29
  • @Maurice return that position of the language_id in the set '1,2' and it will return 0 if the language_id cannot be found ... or NULL when jetc.language_id is NULL. Wouldn't that throw an error? No. The documentation does not provide for this result. I don't understand how you can use the return value (which is a number) for the ORDER BY argument. ?? You don't understand how the numbers are sorted/ordered? by their values... in this particular case in descending order - large forward, small backward, NULLs last.
    – Akina
    Mar 3, 2020 at 12:34
  • I always thought you needed to give a column as the argument for ORDER BY, like ORDER BY jetc.language_id DESC. Right now it looks like the final query may look like ORDER BY 1 DESC because of the FIND_IN_SET function. Or does MySql look at the jetc.language_id part inside of FIND_IN_SET instead of its return value in this case?
    – Maurice
    Mar 3, 2020 at 12:38
  • @Maurice I always thought you needed to give a column as the argument for ORDER BY No. It doesn't matter what is sorted/ordered - fields, values, or the results of calculating expressions. Server will calculate FIND_IN_SET(jetc.language_id, '1,2') expression for each row (think as a hidden column is created), then sort by this value (by hidden column, which will be removed after sorting). Right now it looks like the final query may look like ORDER BY 1 DESC No.
    – Akina
    Mar 3, 2020 at 12:43
  • ah i get it now. Thanks allot @Akina!
    – Maurice
    Mar 3, 2020 at 12:44

Your Answer

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

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