WHERE clause when subtracting between two MySQL columns

Question

I have the following MySQL query, which works absolutely fine:

SELECT a.id, a.event_name, c.name, a.reg_limit-e.places_occupied AS places_available, a.start_date FROM nbs_events_detail AS a, nbs_events_venue_rel AS b, nbs_events_venue AS c, (SELECT e.id, COUNT(d.event_id) AS places_occupied FROM nbs_events_detail AS e LEFT JOIN nbs_events_attendee AS d ON e.id=d.event_id GROUP BY e.id) AS e WHERE a.id=b.event_id AND b.venue_id=c.id AND a.id=e.id AND a.event_status='A' AND a.start_date>=NOW() ORDER BY a.start_date 

However, I am trying to add another WHERE to filter out the results shown in the column created for the subtraction: a.reg_limit-e.places_occupied AS places_available

What I have done so far has been to add a WHERE following form: WHERE places_available>0

However, if I try to use this instruction, the request will fail and the results will not be shown. The error is reported: #1054 - Unknown column 'places_available' in 'where clause'

In a.reg_limit I have numbers, in e.places_occupied I have numbers generated by COUNT in a subquery. What am I missing?

Answer 1

You cannot use the aliases defined in the query in WHERE . To make your request, use the condition a.reg_limit-e.places_occupied>0 in WHERE .

Answer 2

The WHERE is executed before the SELECT , so it does not know about this new alias places_available , the logical order of operations in Mysql :

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING offer
• SELECT clause
• ORDER BY clause

As a workaround for this, you can wrap it in a subquery, for example:

 SELECT * FROM ( SELECT a.id, a.event_name, c.name, a.reg_limit-e.places_occupied AS places_available, a.start_date FROM nbs_events_detail AS a INNER JOIN nbs_events_venue_rel AS b ON a.id=b.event_id INNER JOIN nbs_events_venue AS c ON b.venue_id=c.id INNER JOIN ( SELECT e.id, COUNT(d.event_id) AS places_occupied FROM nbs_events_detail AS e LEFT JOIN nbs_events_attendee AS d ON e.id=d.event_id GROUP BY e.id ) AS e ON a.id=e.id WHERE a.event_status='A' AND a.start_date>=NOW() ) AS t WHERE places_available>0 ORDER BY a.start_date; 

Also try using the ANSI-92 JOIN instead of the old syntax and use the explicit JOIN instead of mixing the conditions in the WHERE , like what I did.

Answer 3

first: another clause must be added by logical logic operators ( AND or OR ) after the first WHERE event. The value is something like:

 SELECT * FROM yourtable WHERE col1 = value AND col2 = value 

second: you put an alias on your wildcard, but this will not work in your WHERE clause the way you intend. You can either write it like this (my evil / bad sentence):

 SELECT a.id, a.event_name, c.name, a.reg_limit-e.places_occupied AS places_available, a.start_date FROM nbs_events_detail AS a, nbs_events_venue_rel AS b, nbs_events_venue AS c, (SELECT e.id, COUNT(d.event_id) AS places_occupied FROM nbs_events_detail AS e LEFT JOIN nbs_events_attendee AS d ON e.id=d.event_id GROUP BY e.id) AS e WHERE a.id=b.event_id AND b.venue_id=c.id AND a.id=e.id AND a.event_status='A' AND a.start_date>=NOW() AND a.reg_limit-e.places_occupied > 0 ORDER BY a.start_date 

or you can use a subquery - which will talk better about performance ... because the functional equations in WHERE clauses are evil and economical. See Mahmoud Gamals in charge of this request ... don't want to enter it here again and don't want to steal it, -)