CODEWITHSUNDEEP
mysqlsql
stressed out
stressed out

Reputation: 552

Run different SELECT commands in MySQL depending on the result of a previous query

Suppose that I have two tables in my database: table1 and table2. Table1 has a column that stores an ID that is referenced to a record in table2 with that ID.

Now I'm going to select some records and store them in a temporary table for future reference:

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS (
SELECT table1.table2_id, SUM(table1.quantity) AS qty
FROM table1
WHERE table1.table2_id IN (SELECT table2.id FROM table2)
GROUP BY table1.table2_id
);

What this query does is not that important for what I am going to ask later. So, don't spend too much time trying to understand it. I have included it here only for completeness.

Obviously, that query will either return no records at all or it will return some records.

If it returned no records, I want to run my second query which looks like this

SELECT table1.*, 0 
FROM table2 INNER JOIN temp_table
ON table2.id = temp_table.table2_id;

Otherwise, if my query had returned some records, I want to run this query which uses the column "qty" from my temporary table:

SELECT table1.*, qty
FROM table2 INNER JOIN temp_table
ON table2.id = temp_table.table2_id;

So, here is my question: I am looking for a MySQL solution that first runs the first query, then depending on whether it has returned something or not, runs the second or the third query.

I tried to use an IF ELSE statement by following MySQL documentations, but it said Unrecognized statement type. (near "IF" at position 0). I am new to SQL programming. Any help is appreciated. Thanks in advance.

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

I am interpreting the difference as only being in the select -- so the second query refers to table1 throughout, not table2.

If so, you want a LEFT JOIN:

SELECT t1.*, COALESCE(qty, 0)
FROM table1 t1 LEFT JOIN
     temp_table tt
     ON t1.id = tt.table1_id;

Upvotes: 2

Related Questions