What is the issue in below sql query? [closed]

I am beginner in sql. Anyone please tell me the problem of my customised query.

select * 
from Follow_My_Doct.tbl_hospital_registration h 
INNER JOIN Follow_My_Doct.tbl_subscribed_packages s 
      ON  s.hospitalId = h.id 
      AND s.active = 1 
      AND ( select max(sp.expireDate), sp.hospitalId 
            from Follow_My_Doct.tbl_subscribed_packages sp
            where sp.active = 1 
            group by sp.hospitalId ) 
where h.hospital_active = 1

Error Code: 1241. Operand should contain 1 column(s)

Subscription table

hospital Id expireDate
145         2021-07-10
146         2021-06-10
147         2021-09-10
146         2021-10-10

Answer

You should put that subquery with the max and group by inside an INNER JOIN clause.

select * 
from Follow_My_Doct.tbl_hospital_registration h 
INNER JOIN ( select max(sp.expireDate) maxexpiredate, sp.hospitalId 
            from Follow_My_Doct.tbl_subscribed_packages sp
            where sp.active = 1 
            group by sp.hospitalId ) as s 
      ON  s.hospitalId = h.id 
where h.hospital_active = 1

Since I don’t have your data tables, I made up one environment to test that query by using table variables. The example below is for SQL Server, but the query works fine for MySQL, which currently I don’t have installed on my machine.

declare @tbl_subscribed_packages TABLE(
  hospitalId int, 
  active bit, 
  expiredate datetime
)

declare @tbl_hospital_registration table(
 id int,
 hospital_active bit)

Now populate tables with data:

insert @tbl_hospital_registration 
values (145,1),(146,1),(147,1)

insert @tbl_subscribed_packages 
values (145,1,'2021-07-10')
,(146,1,'2021-06-10')
,(147,1,'2021-09-10')
,(146,1,'2021-10-10')

Then, I test the query against these data

select * 
from @tbl_hospital_registration h 
INNER JOIN ( select max(sp.expireDate) maxexpiredate, sp.hospitalId 
            from @tbl_subscribed_packages sp
            where sp.active = 1 
            group by sp.hospitalId ) as s 
      ON  s.hospitalId = h.id 
where h.hospital_active = 1

Note that using subquery as a view in INNER JOIN, I should add an alias name for max(expireDate) column. The result is:

id	hospital_active	maxexpiredate	hospitalId
145	1	2021-07-10 00:00:00.000	145
146	1	2021-10-10 00:00:00.000	146
147	1	2021-09-10 00:00:00.000	147

Is that what you want?