Archish Archish - 7 months ago 16
SQL Question

Issues using function FOUND_ROWS() giving wrong result with join

This is my query.

SELECT SQL_CALC_FOUND_ROWS (FOUND_ROWS() ) as total,
(SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization
WHERE dsp.profileid = pm.id and
(dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec ,
pm.id as profileid, pm.loginid as loginid, dam.clinicname, dam.area, dam.address, dam.pincode, dam.id as
addressid, dam.feecharge as feecharge, pm.fname, pm.lname, pm.email, pm.mobile, pm.phone, pm.gender, pm.dob,
pm.totexp, pm.imagepath, pm.languages, pm.statement, pm.createdby, um.profile_url, um.clinic_url,
pm.hsbit, (SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu
FROM Profile_Master pm LEFT JOIN DAddress_Master dam on dam.profileid = pm.id left join Unique_Url_Master um on
um.clinicid =dam.id WHERE dam.city='Surat' and pm.id IN (SELECT profileid FROM DSpecialization_Master
WHERE specialization = (select id from Specialization_master where specialization='Dentist')) ORDER BY pm.id limit 0 , 10


accepted result is
total:344
, instated i am getting
total:1
.

I have execute this query and I am getting correct result.

select SQL_CALC_FOUND_ROWS id,(FOUND_ROWS() ) as total from unique_url_master1 limit 10;


total:1313


What is wrong I am doing? FYI I can't break query i.e first execute


select SQL_CALC_FOUND_ROWS


and execute


select FOUND_ROWS()


need help so badly..

EDIT

I have changed my query and use count(*) function. Now I am getting correct total:344 but it return only one row. Now can someone guide me?

SELECT count(*) as total_count ,
(SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid = pm.id and (dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec,
pm.id as profileid,
pm.loginid as loginid,
dam.clinicname,
dam.area,
dam.address,
dam.pincode,
dam.id as addressid,
dam.feecharge as feecharge,
pm.fname,
pm.lname,
pm.email,
pm.mobile,
pm.phone,
pm.gender,
pm.dob,
pm.totexp,
pm.imagepath,
pm.languages,
pm.statement,
pm.createdby,
um.profile_url,
um.clinic_url,
pm.hsbit,
(SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu
FROM Profile_Master pm
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id
WHERE dam.city='Surat'
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist'))
ORDER BY pm.id
LIMIT 0, 10;


I have already working query in MSSQL with use of COUNT(*) OVER(), because of requirement change I have to convert MSSQL query into MYSQl. the result I am accepting this like this this

Answer

You're using an aggregating function COUNT without n GROUP BY this will always result in one row. Definition of GROUP BY.

It would be much better to just use 2 separate queries to get what you want, one you select all the other variables and in one you only select the count

All rows+variables

SELECT  (SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid = pm.id and (dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec, 
    pm.id as profileid, 
    pm.loginid as loginid, 
    dam.clinicname, 
    dam.area, 
    dam.address, 
    dam.pincode, 
    dam.id as addressid, 
    dam.feecharge as feecharge, 
    pm.fname, 
    pm.lname, 
    pm.email, 
    pm.mobile, 
    pm.phone, 
    pm.gender, 
    pm.dob, 
    pm.totexp, 
    pm.imagepath, 
    pm.languages, 
    pm.statement, 
    pm.createdby, 
    um.profile_url, 
    um.clinic_url, 
    pm.hsbit,  
    (SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu 
FROM Profile_Master pm 
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id 
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id 
WHERE dam.city='Surat' 
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist')) 
ORDER BY pm.id 
LIMIT 0, 10;

Count:

SELECT  count(*) as total_count,
FROM Profile_Master pm 
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id 
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id 
WHERE dam.city='Surat' 
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist')) 
ORDER BY pm.id 
LIMIT 0, 10;

If you really want it like your image in one query, you have to do the following:

SELECT T1.overall_count, T2.*
FROM (SELECT COUNT(*) AS overall_count
    FROM Profile_Master pm 
    LEFT JOIN DAddress_Master dam on dam.profileid = pm.id 
    LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id 
    WHERE dam.city='Surat' 
    AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist')) 
    ORDER BY pm.id 
    LIMIT 0, 10) AS T1
JOIN ((SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid = pm.id and (dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec, 
    pm.id as profileid, 
    pm.loginid as loginid, 
    dam.clinicname, 
    dam.area, 
    dam.address, 
    dam.pincode, 
    dam.id as addressid, 
    dam.feecharge as feecharge, 
    pm.fname, 
    pm.lname, 
    pm.email, 
    pm.mobile, 
    pm.phone, 
    pm.gender, 
    pm.dob, 
    pm.totexp, 
    pm.imagepath, 
    pm.languages, 
    pm.statement, 
    pm.createdby, 
    um.profile_url, 
    um.clinic_url, 
    pm.hsbit,  
    (SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu 
FROM Profile_Master pm 
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id 
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id 
WHERE dam.city='Surat' 
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist')) 
ORDER BY pm.id 
LIMIT 0, 10) AS T2
Comments