SQL - If exists does not work with Select Statements?

Question

When I add IF EXISTS to check if any records exists to below query I do not get any output. On removing IF EXISTS the query works fine. I am not sure what is the issue

I am only trying to check if there are any records present, if yes display them

Query

async getData() {
        try {
            const data = await this._conn.query(`

IF EXISTS(select first_name.value_name,quiz_table.answer, windows,player,first_name.value_id,country_place,current_name, pet_name, marker, relations
from schema_name.plugin,schema_name.quiz_table,schema_name.first_name, schema_name.value_version, schema_name.relationss
where (plugin.answer= quiz_table.answer
and quiz_table.windows=first_name.value_id
and marker is not null
and schema_name.value_version.value_id= schema_name.first_name.value_id
and schema_name.value_version.caste= schema_name.first_name.caste
and schema_name.value_version.value_name= schema_name.first_name.value_name
and schema_name.value_version.version_number= schema_name.first_name.version_number
and schema_name.relationss.value_id= schema_name.first_name.value_id
and schema_name.relationss.caste= schema_name.first_name.caste
and schema_name.relationss.value_name= schema_name.first_name.value_name))
limit 1;)
}

`IF` statement can be used in compound statements only (procedure, trigger, etc.). — Akina, Apr 16 '21 at 15:19
Okay, what else can I use here? I have 2 select statement. I am looking for `if - else` kind of logic to display from any 1 select if data is present — user12707940, Apr 16 '21 at 15:19
*I am only trying to check if there are any records present* `SELECT EXISTS (subquery)`. — Akina, Apr 16 '21 at 15:19
https://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists — MatBailie, Apr 16 '21 at 15:20
The problem is both select statements `JOINS` different columns — user12707940, Apr 16 '21 at 15:20
You have two StackOverflow users? WHY??? As for what you want, just put the `IF` in your application code, not the SQL. — MatBailie, Apr 16 '21 at 15:21
@MatBailie I could not post another question on old account. Okay I will try using `If` — user12707940, Apr 16 '21 at 15:22
@user12707940 Fetch two resultsets and decide which one contains the data. Also you can use `UNION ALL` and order them by some artificial constant added to `UNION` which will put them in desired order. — astentx, Apr 16 '21 at 15:30

score 0 · Answer 1 · answered Apr 16 '21 at 15:40

Simply use the query to select data, so start with

SELECT first_name.value_name, quiz_table.answer, ...

without the IF EXISTS, and, as suggested by @MatBailie in comments, use the if-else statement in your application code, checking the content of the resultset returned by the query. Then display data, if present.

For example, in PHP:

$query = "select first_name.value_name,quiz_table.answer, windows,player,first_name.value_id,country_place,current_name, pet_name, marker, relations
        from schema_name.plugin,schema_name.quiz_table,schema_name.first_name, schema_name.value_version, schema_name.relationss
        where plugin.answer= quiz_table.answer
        and quiz_table.windows=first_name.value_id
        and marker is not null
        and schema_name.value_version.value_id= schema_name.first_name.value_id
        and schema_name.value_version.caste= schema_name.first_name.caste
        and schema_name.value_version.value_name= schema_name.first_name.value_name
        and schema_name.value_version.version_number= schema_name.first_name.version_number
        and schema_name.relationss.value_id= schema_name.first_name.value_id
        and schema_name.relationss.caste= schema_name.first_name.caste
        and schema_name.relationss.value_name= schema_name.first_name.value_name
";
$resultset = mysqli_query($conn, $query);

// error checking code ...

if ($resultset->num_rows > 0) {  // here is where you check the IF EXISTS
    // display data ...
}

SQL - If exists does not work with Select Statements?

1 Answers1