0

I'am tired looking for this issue on internet, I don't know what a keyword for search for that, and I can`t find same issue for my issue.

Ok to the point.

select id, name,

(select count(tableB.id) from tableB where tableB.id_tableA = tableA.id) as haveBonA,

(select count(tableC.id) from tableC where tableC.id_tableA = tableA.id) as haveConA,

IF(haveBonA=haveConA,"Equal","Not Equal") AS equalTableBTableC

from tableA;

If this query run output error : Unknown column 'haveBonA' in 'field list'

I don't know how to express it. and I'm sorry, can't speak english. Thank Your.

SOLVED

select id, name,

(select count(tableB.id) from tableB where tableB.id_tableA = tableA.id) as haveBonA,

(select count(tableC.id) from tableC where tableC.id_tableA = tableA.id) as haveConA,

(select (IF(haveBonA=haveConA,"Equal","Not Equal") AS equalTableBTableC)

from tableA;

I change to (select (IF(haveBonA=haveConA,"Equal","Not Equal") AS equalTableBTableC) Thank you for all answering.

0

You can try this

with t1 as (
select id, name,
(select count(tableB.id) from tableB where tableB.id_tableA = tableA.id) as 
haveBonA,
(select count(tableC.id) from tableC where tableC.id_tableA = tableA.id) as 
haveConA
from tableA
)

select t1.*,
   case
   when t1.haveBonA = t1.haveConA then "Equal"
   else "Not Equal"
   end equalTableBTableC
from t1
where 1=1
|improve this answer|||||
0

What is the version you are running?

If you are running version 8.0 or above then you can use common table expression (WITH clause in the beginning of query) as mentioned by kevinnwhat

If you are running lower than this like 5.7 or so, you may re-write this query as :

SELECT 
    id,
    name,
    IF(haveBonA = haveConA,
        'Equal',
        'Not Equal') AS equalTableBTableC
FROM
    (SELECT 
        id,
            name,
            (SELECT 
                    COUNT(tableB.id)
                FROM
                    tableB
                WHERE
                    tableB.id_tableA = tableA.id) AS haveBonA,
            (SELECT 
                    COUNT(tableC.id)
                FROM
                    tableC
                WHERE
                    tableC.id_tableA = tableA.id) AS haveConA
    FROM
        tableA) tab;

Regards

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.