CODEWITHSUNDEEP
mysqlsql
Vish
Vish

Reputation: 4492

SQL Error (unknown column in having clause).

[SQL]SELECT
vlog.lead_id, vlog.phone_number
FROM
vicidial_log as vlog
GROUP BY vlog.lead_id
HAVING(
CASE 
WHEN vlog.status = 'NA' THEN 1 
WHEN vlog.status = 'ADC' THEN 1 
END
) = COUNT(*)

[Err] 1054 - Unknown column 'vlog.status' in 'having clause'

The query above is not working. I am trying to achieve this: https://www.db-fiddle.com/f/ej4fM8GptBk9FvGJC8AkUH/0 in sense.

 [SQL]SELECT
    vlog.lead_id, vlog.phone_number, vlog.status
    FROM
    vicidial_log as vlog
    GROUP BY vlog.lead_id
    HAVING(
    CASE 
    WHEN vlog.status = 'NA' THEN 1 
    WHEN vlog.status = 'ADC' THEN 1 
    END
    ) = COUNT(*)

THIS WORKS BUT IT DOESN'T GIVE ME THE RESULTS I WANT

status = can be anything lead_id = not unique phone_number = not unique

I am trying to find phone numbers WHERE atleast the status is either

ADC
NA
OR BOTH ADC AND NA

Any other combination should not return the phone number.

Upvotes: 0

Views: 1194

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37367

It's shot in the dark, but maybe it will work for you. Change your HAVING clause to:

HAVING SUM(CASE WHEN vlog.status in ('NA','ADC') THEN 1 ELSE 0 END) = COUNT(*)

Upvotes: 2

Aaron Dietz
Aaron Dietz

Reputation: 10277

It seems like this would work:

SELECT DISTINCT lead_id, phone_number
FROM vicidial_log vlog
WHERE status IN ('NA', 'ADC')
AND NOT EXISTS (SELECT *
                FROM vicidial_log vlog2
                WHERE status NOT IN ('NA','ADC')
                AND vlog.lead_id = vlog2.lead_id
                AND vlog.phone_number = vlog2.phone_number)

It's not 100% clear whether you need the comparison to be on both lead_id and phone_number though.

Upvotes: 1

Related Questions