The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

subquery in FROM clause not working

hi all, i have portable version of XAMPP v3.3.0 (showing on its control panel), mysql/mariadb version is: mysql Ver 15.1 Distrib 10.4.22-MariaDB, for Win64 (AMD64), to connect and work on database related tasks i am using SQLyog community version.

i wrote a SQL statement and it is working fine as below:

SELECT applicant_id, COUNT(*) nn FROM memberships WHERE memberships.status = 'Active' GROUP BY applicant_id HAVING COUNT(*)>1

but when i tried to select only column applicant_id from the result set, SQLyog throwing error, my attempt is as below:

SELECT applicant_id FROM ( SELECT applicant_id, COUNT(*) nn FROM memberships WHERE memberships.status = 'Active' GROUP BY applicant_id HAVING COUNT(*)>1) a

on top of this SQL i want to add another actual query to create a report for multiple occurances.

SQLyog is not showing actual error and asking me to read manual as below:

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 0, 1000' at line 3

please help, how i can get the required data? what are the limitations or i am doing this wrong way?

regards

Answer Answered by Ahmed Haroon in this comment.

its working fine now, just formatted and added alias to subquery as below:

SELECT applicant_id FROM (SELECT applicant_id, COUNT(*) nn FROM memberships WHERE memberships.status = 'Active' GROUP BY applicant_id HAVING COUNT(*)>1) t

regards

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.