ajoo Posted August 10, 2016 Share Posted August 10, 2016 (edited) Hi all, The query below works great // This creates a temporary table CREATE TEMPORARY TABLE tmp_result AS SELECT xid FROM ( ( SELECT mr.xid as xid FROM mr, ar WHERE mr.mid = ar.xid && ar.aid = 94 ) UNION ( SELECT ar.xid as xid FROM ar WHERE ar.aid = 94 && ar.role <> 'master' ) )tt; // This finds the required record SELECT xid,bb.fname, bb.lname, bb.city, bb.cell, bb.email, bb.fid, bb.center_No from tmp_result INNER JOIN ( SELECT fd.fname, fd.lname, fd.fran_id, fd.city, fd.cell, fd.email, sd.fid, sd.center_No From sd, fd WHERE sd.fid = fd.fran_id ) bb ON bb.fran_id = tmp_result.xid and outputs the result shown in the attachment. However if i wish to have a calculated field displayed as a part of the result, it returns an empty set. Here is the one that returns the empty set with the simple addition ( count(fid) total ) SELECT xid,bb.fname, bb.lname, bb.city, bb.cell, bb.email, bb.fid, bb.center_No, bb.total from tmp_result INNER JOIN ( SELECT fd.fname, fd.lname, fd.fran_id, fd.city, fd.cell, fd.email, sd.fid, sd.center_No, count(fid) total From sd, fd WHERE sd.fid = fd.fran_id ) bb ON bb.fran_id = tmp_result.xid I have tried count(sd.fid) total as well. Same result. I have a similar query without a join and which does not use a temporary table where this and another calculated field works just fine. Kindly let me know how to solve this. Thanks all ! Edited August 10, 2016 by ajoo Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted August 10, 2016 Solution Share Posted August 10, 2016 Using an aggregation function (SUM(), COUNT() etc) without specifying a GROUP BY column(s) will return a single row containing the aggregate for the whole selection. Values for the other non-aggregated selected columns are indeterminate (although usually from the first record in the set. Quote Link to comment Share on other sites More sharing options...
ajoo Posted August 10, 2016 Author Share Posted August 10, 2016 Guru Barand !!!!!!!! Thank you ! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.