Mysql inner query not working

Question

    $sql= "select 
         tblservergroup.id,
         tblservergroup.groupname,

         if(tblservergroup.defserverpaid='0', '0', select concat(tblservers.ipaddress, ' (', tblservers.name, ')' ) as defaultserverpaiddesc from tblservers where tblservers.id=tblservergroup.defserverpaid),
         if(tblservergroup.defserverfree='0', '0', select concat(tblservers.ipaddress, ' (', tblservers.name, ')' ) as defaultserverfreedesc from tblservers where tblservers.id=tblservergroup.defserverfree),  

         tblservergroup.note
         from tblservergroup
";

This similar concept can be done in MSSQL, but when I write it in mysql, it shows error. Help.

Answer 1

If you insist on subqueries, you can do this:

SELECT
    tblservergroup.id,
    tblservergroup.groupname,
    tblservergroup.note,
    IF(defserverpaid='0', '0', (SELECT concat(ipaddress, ' (', name, ')') FROM tblservers WHERE id=defserverpaid)) AS defaultserverpaiddesc,
    IF(defserverfree='0', '0', (SELECT concat(ipaddress, ' (', name, ')') FROM tblservers WHERE id=defserverfree)) AS defaultserverfreedesc
FROM
    tblservergroup

I'd suggest to use a LEFT JOIN instead:

SELECT
    tblservergroup.id,
    tblservergroup.groupname,
    tblservergroup.note,
    IF(defserverpaid='0', '0', concat(ts1.ipaddress, ' (', ts1.name, ')')) as defaultserverpaiddesc
    IF(defserverfree='0', '0', concat(ts2.ipaddress, ' (', ts2.name, ')')) as defaultserverfreedesc
FROM
    tblservergroup
    LEFT JOIN tblservers ts1 ON ts1.id=defserverpaid
    LEFT JOIN tblservers ts2 ON ts2.id=defserverfree

Ideally also store NULL instead of 0 in defserverpaid/defserverfree, and add foreign key constraints.