Using a custom field in WHERE clause of SQL query

Question

My question is very similar to this one How to reference a custom field in SQL I have the following query:

SELECT * , (SELECT COUNT( id ) 
FROM cms_store_items
WHERE speaker = cms_store_items_speakers.id
) AS count
FROM cms_store_items_speakers
LIMIT 0 , 30

I need to add a WHERE clause that looks like WHERE count > 0 but when I do I get the error Unknown column 'count' in 'where clause' is there anyway for me to reference the custom field in my where clause without duplicating logic?

I could just place the logic for the where clause in my code but I don't want to send what may well be more then 1000 rows to the app if not needed, it just seems like a waste of resources.

Michael Buen · Accepted Answer · 2009-08-19 05:54:04Z

24

Well, to do this strictly the way you're doing it:

select
*
from
(
    SELECT * , (SELECT COUNT( id ) 
    FROM cms_store_items
    WHERE speaker = cms_store_items_speakers.id
    ) AS count
    FROM cms_store_items_speakers
) a
where a.count > 0
LIMIT 0 , 30

It would probably be better to do the following, though. It makes good use of the having clause:

select
    s.id,
    s.col1,
    count(i.speaker) as count
from
    cms_store_items_speakers s
    left join cms_store_items i on
        s.id = i.speaker
group by
    s.id,
    s.col1
having
    count(i.speaker) > 0
limit 0, 30

edited Aug 19, 2009 at 5:54

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

answered Aug 19, 2009 at 1:57

Eric

95.7k13 gold badges117 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

UnkwnTech Over a year ago

First option works great, seems my earlier (now deleted comment was due to caching), thanks for the solution.

Michael Buen Over a year ago

change the count(*) to count(i.speaker), and the having count(*) to having count(i.speaker), just future-proof the code; if the need to count zero matches arises, then just change the inner join to left join, i'll upvote your answer :)

too much php · Accepted Answer · 2009-08-19 02:05:53Z

16

You can using HAVING clause instead:

...
) AS count
FROM cms_store_items_speakers
HAVING count > 0
LIMIT 0 , 30

HAVING is like WHERE but it is able to work on columns which are computed. Warning: HAVING works by pruning results after the rest of the query has been run - it is not a substitute for the WHERE clause.

answered Aug 19, 2009 at 2:05

too much php

91.5k37 gold badges133 silver badges140 bronze badges

Comments

LorenVS · Accepted Answer · 2009-08-19 01:58:17Z

0

I'm not 100% sure about mysql, but something like this should be very possible:

(SELECT 
     *
FROM
(
   SELECT cms_store_items_speakers.*,
          (SELECT COUNT(id) FROM cms_store_items
           WHERE speaker = cms_store_items_speakers.id) AS 'count'
   FROM cms_store_items_speakers)
   LIMIT 0, 30
)
WHERE count > 0;

answered Aug 19, 2009 at 1:58

LorenVS

12.9k10 gold badges49 silver badges57 bronze badges

Collectives™ on Stack Overflow

Using a custom field in WHERE clause of SQL query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related