Mysql query IF statement not working correctly

Question

Can someone please help me with this query:

SELECT
    w.aboutme,
    w.user_id,
    IF ( (SELECT db2.user_relation.id FROM db2.user_relation
          WHERE w.user_id = db2.user_relation.user_id AND
          db2.user_relation.clients_id = 1), "true", "false") as selected,
    (SELECT GROUP_CONCAT(DISTINCT name SEPARATOR ", ") FROM services
     WHERE w.user_id = w.user_id) as services
FROM websites w, db2.user_relation

Everything works fine if db2.user_relation has some records but if it's empty the query returns 0 results, even though there are 4 records in the websites table.

And everything works if I take the IF statement out. So it appears my IF statement needs some tweaking.

Thanks for any help!

Peter Lang · Accepted Answer · 2018-02-20 14:35:24Z

1

Looks like you should remove db2.user_relation from your FROM clause:

FROM websites w

You do not use any columns from it, and if no rows exist, no rows are returned by your query.

answered Feb 20, 2018 at 14:35

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bstras21 Over a year ago

Thanks, geez so easy

John Woo · Accepted Answer · 2018-02-20 14:39:09Z

Your query is much better written in the form of JOIN that using correlated subqueries.

SELECT  w.aboutme, 
        w.user_id,
        CASE WHEN u.id IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END AS Selected
        GROUP_CONCAT(DISTINCT name SEPARATOR ', ') as services
FROM    websites w
        LEFT JOIN db2.user_relation u
            ON  w.user_id = u.user_id 
                AND u.clients_id = 1
        LEFT JOIN services s
            ON w.user_id = s.user_id
GROUP   BY  w.aboutme, 
            w.user_id

In your original query, the reason why you are getting empty rows when db2.user_relation has no row is because you are performing cartesian join (CROSS JOIN) between the two tables, so if one of the tables is empty then obviously the final result is also empty.

FROM websites w, db2.user_relation is the same as FROM websites w CROSS JOIN db2.user_relation

Collectives™ on Stack Overflow

Mysql query IF statement not working correctly

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related