I need to know how i can write IF statements and CASE break statements that use and execute queries, etc in MySQL ?

Question

I need to execute a query that is highly dependent on several conditions what need to be checked by analyzing that data in other tables, the base goal is to return the rows from the tables if all of the conditions in the other tables are true, I am fed up with INNER joins LEFT joins and WHERE statement, i need to look up one table, if the returned value is 1, 0 or 4 or a set of values, i need to execute an other statement, and based on the resuts of that i need to execute one last statement which is my final result.

as far as functions are procedures are concerned, i studies the MySQL documentation like hell and all it gives me is this ::

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `SimpleCompare`(n INT, m INT) RETURNS varchar(20) CHARSET latin1
BEGIN

    DECLARE s VARCHAR(20);

    IF n > m THEN SET s = '>';

    ELSEIF n = m THEN SET s = '=';

    ELSE SET s = '<';

    END IF;

    SET s = CONCAT(n, ' ', s, ' ', m);

    RETURN s;

  END

Now this is so plain, i dont even know where to start, I the "returns varchar(20)" what does it need to be if im expecting it to return a table of 10 rows and not a VARCHAR(20), what do I declare "DECLARE s VARCHAR(20);" as if i want it to be a table not a VARCHAR(20).

the (n > m) after the "IF" how to i replace it with my own query , and after I do that, the "THEN SET s = '>'" how do i set s = to the query results ?, this is driving me crazy the syntax is beyond me, and the documentation does not explain anything.

Thanks in advance

Answer 1

To my knowledge, MySQL doesn't support a table valued data type. The use of the function you posted would be:

SELECT simplecompare(yt.n, yt.m) AS eval
    FROM YOUR_TABE yt

...which would return:

eval
--------
1 = 1
2 < 3
etc.

SQL is set based, which is different from typical programming (procedural or OO).