Filtering MYSQL query results based on the result of an alias (AS)

Question

Filtering MYSQL query results based on the result of an alias (AS)

Here is a MySQL query that I'm working with:

SELECT
     t1.*,
    (SELECT count(*) FROM tb_pt t2
     WHERE t2.id_provinsi = t1.id_provinsi && nama_pt LIKE '%$nama%' && status_pt = 1 ) As jumlah
FROM tb_provinsi t1
WHERE status_provinsi = 0

The above query will produce the following result:

https://i.stack.imgur.com/bR03w.png

I am facing an issue where the query returns all results regardless of whether jumlah is 0 or not. I would like to filter these results by adding 'WHERE jumlah != 0' so that the query only returns one result where jumlah is greater than 0 (id_provinsi = Prv02).

Any suggestions or ideas on how to accomplish this? Thank you.

mysql json

Answer 1

Answer №1

The underlying issue causing your challenge is the unavailability of an alias in the SELECT statement when it's needed in the subsequent WHERE clause execution. You can address this by duplicating your alias logic within the WHERE clause, or utilize MySQL's enhanced HAVING operator which allows referencing aliases:

SELECT
     t1.*,
    (SELECT count(*) FROM tb_pt t2
     WHERE t2.id_provinsi = t1.id_provinsi AND
           nama_pt LIKE '%$nama%' AND
           status_pt = 1) AS jumlah
FROM tb_provinsi t1
WHERE status_provinsi = 0
HAVING jumlah > 0;

Answer 2

The underlying issue causing your challenge is the unavailability of an alias in the SELECT statement when it's needed in the subsequent WHERE clause execution. You can address this by duplicating your alias logic within the WHERE clause, or utilize MySQL's enhanced HAVING operator which allows referencing aliases:

SELECT
     t1.*,
    (SELECT count(*) FROM tb_pt t2
     WHERE t2.id_provinsi = t1.id_provinsi AND
           nama_pt LIKE '%$nama%' AND
           status_pt = 1) AS jumlah
FROM tb_provinsi t1
WHERE status_provinsi = 0
HAVING jumlah > 0;

Filtering MYSQL query results based on the result of an alias (AS)

Answer №1

Similar questions

What is the best method to retrieve a JSON record by its specific ID using Angular's AJAX functionalities?

Is there a way to convert the structure of HTML/CSS into JSON format?

How can I retrieve a specific key from a nested array while using ng-repeat to iterate through it

How can TypeScript objects be serialized?

Incorporating Hive SerDe jar into SparkSQL Thrift Server

Utilizing the SELECT last_insert_id method for generating distinct and sequential identifiers

What is the best way to extract parameters from a JSON object?

PHP is used to download JSON data in mobile applications

Is there a way to toast the values of an array retrieved from a getter within my object?

Instructions on removing rows by using buttons within a JavaScript-generated table

Step-by-step guide on integrating node.js and MySQL to store data from an online form in a database

The attempt to fetch the submitted data via the PHP URL was unsuccessful

Strategies for managing multiple openIDs linked to a single user account

How to extract JSON data enclosed within HTML tags in Android application

What is the reason behind only the initial click boosting the vote count while subsequent clicks do not have the same

What is the best way to clear an array?

Is there a method to automatically replace the latest or asterisk (*) symbol in package.json with a specific version number?

How can jq be used to compactly format specific fields?

Nested association in Rails for rendering as JSON is a powerful feature that allows you

Troubleshooting a 400 Bad Request Error