CODEWITHSUNDEEP
mysqlsql
Jerielle
Jerielle

Reputation: 7520

How can I get the result value in a subquery and use it for comparison using MySQL?

I am trying to get the total bid of all product. And I want to display only the total bid that are greater than or equal to 10. Here's my query.

SELECT 
p.id AS id,
pd.product_name AS product_name,
p.cover_image AS cover_image,
p.starting_price AS starting_price,
p.final_price AS final_price,
p.datetime_end AS datetime_end,
(SELECT COUNT(id) FROM auction_product_bidding AS pd WHERE pd.product_id = p.id) AS total_bid
FROM auction_product AS p
LEFT JOIN auction_product_detail AS pd
ON(p.id = pd.product_id)
WHERE p.`status` = 0
AND total_bid >= 10

And my error is: unknown column total bid... Can I achieve what I want using a query or should I do this process in my PHP side?

That's all guys I hope you can help me. Thanks.

Upvotes: 0

Views: 23

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269913

In most databases you would use a subquery -- because column aliases are not recognized in the where clause. MySQL extends the HAVING clause for this purpose, so you can write:

SELECT p.id, pd.product_name, p.cover_image, p.starting_price,     
       p.final_price, p.datetime_end,
       (SELECT COUNT(id) FROM auction_product_bidding pd WHERE pd.product_id = p.id) as total_bid
FROM auction_product p LEFT JOIN
     auction_product_detail pd
     ON p.id = pd.product_id
WHERE p.status = 0
HAVING total_bid >= 10

Upvotes: 2

Related Questions