CODEWITHSUNDEEP
mysqlcasealiashaving
ccdpowell
ccdpowell

Reputation: 659

MySQL Alias and Field have same name - Which is evaluated in Having clause?

I have a mysql query that is joining phone numbers from multiple tables that I can not edit and I need to create an output with the same field name as one of the tables. I'm using a CASE statement to do this. Here is a simple version of my query:

SELECT t1.`id` as id,
CASE 
    WHEN t1.`has_company_phone` = 1  THEN t1.`company_phone`
    ELSE t2.`phone`
END AS `phone`
FROM `input_table1` t1, 
LEFT OUTER JOIN `input_table2` t2
    ON (t2.`id` = t1.`matched_company_id`)
HAVING phone IS NOT NULL;

EDIT: In this query, in the HAVING clause is t2.phone being evaluated, the alias phone, or both? My goal is to have the alias evaluated in this having clause.

EG: t1.company_phone exists, but t2.phone IS NULL. I want to include this record using the t1.company_phone and have the alias name be phone. If I change the HAVING to a WHERE clause, this record will not show up.

Upvotes: 1

Views: 1020

Answers (4)

Boody
Boody

Reputation: 144

with union, you will simply do the following:

select distinct id,  case when phone1 = '' then phone2 else phone1 end as phone from
(select matched_company_id as id, company_phone as phone1, '' as phone2 from `input_table1`
union 
select id as id, '' as phone1, phone as phone2 from `input_table2`) as t
where phone <> ''

then you can add any other table or field similarly.

Upvotes: 0

Dylan Su
Dylan Su

Reputation: 6065

In MySQL, HAVING clause will always search the needed item from the select list.

Once found in the select list, the found item will be used.

If Not found, error is thrown.

Update:

Actually, in ur SQL, you should replace HAVING with WHERE, since it is the same as WHERE. Using WHERE, the confusion will be gone. WHERE clause will always use real table column, not the alias.

Upvotes: 2

ccdpowell
ccdpowell

Reputation: 659

In this case, I took Shadow's advice and constructed a test to see what would happen. I filled t2 with a NULL phone and put a phone in t1.

The alias is evaluated because the record from t1 was included in my results.

I then reversed the values so t1.company_phone was NULL and t2.phone had a value, and reran query. The t2.phone field name was not evaluated as no results showed up after reversing the data.

Boody's advice is probably a more explicit way of getting the functionality desired, but in my case I have about 20fields that will be merged over multiple cases and 4 tables, so the query would get really messy being that explicit.

Upvotes: 0

Boody
Boody

Reputation: 144

replace you having by the following one:

group by t1.`has_company_phone`, t2.`phone`
HAVING CASE 
    WHEN t1.`has_company_phone` = 1  THEN t1.`company_phone`
    ELSE t2.`phone`
END is not null

and be careful to not miss the group by.

or, you can use the condition in the where without group by.

where CASE 
    WHEN t1.`has_company_phone` = 1  THEN t1.`company_phone`
    ELSE t2.`phone`
END is not null

Upvotes: 1

Related Questions