ajoo

Hi Guru Barand, I hope that in my last reply I have cleared all the mysteries that benanamen was referring to. If there is still something that you would like to ask about I'll be happy to answer the best I can. Thanks.

Hi Guru Jacques, Guru Barand & Benanamen, @Guru Jacques ! To repeat what Guru Barand said, I am also very glad that you dropped by this thread. I tried to explain as much as I could, the rules and everything, with diagrams that I made, but I am pained if by "Ajoo isn't making it very clear", Guru Barand is implying that I was doing that on purpose. I tried my best to provide all the information. Glad that you could explain it to here like only you could have. @ Benanamen :- The tying up takes place in the tables Admins, Masters and Franchisee when the permissions have been all granted in the recruitment tables. Those are not there in the SQL dump. I thought that was amply obvious because of the simplicity of the table. I was wrong. You could have asked again. The +ve and -ve values are a system of weights to determine if all permissions have been provided. The -ve values are used to deactivate or revoke permissions. @ Jacques SIr if you can elaborate on this I will be grateful. Thanks again Guru Jacques, Guru Barand and Benanamen.

Hi Benanamen ! Thanks for your inputs, suggestions and effort. No sweat ! We'll pick it up some other time. Thank you.

Hi Benanamen, Yes each recruits directly the ones below it. So the Boss does not recruit the franchisee. In case the recruits do does not get all their permissions, their ID is not inserted into their individual tables. ( Check the picture- there are three table there at the bottom ). Thank you

Hi Kicken ! yes thanks !

Hi Guru Jacques !!! Thank you sir, Coincidentally I had just tried the 0 - offset this morning & found it working and also read about the usage of the GROUP since I was unsure of it and I have been using it like thinking it would be a SORT ON kind of function. Thanks Loads !

ok so i thought that GROUP by is creating an issue and I removed that from the query. And guess what :- SELECT fd.fname, fd.lname, fd.role, fd.city, fd.cell, fd.email, fd.fid From fd WHERE fd.recruiter = 118 LIMIT 1, 10 still returns an empty set. . If I simply remove the LIMIT 1, 10, I get my row of result. I have never had this sort of problem ?? In fact I use LIMIT in most of my queries. Please someone explain to me what's going on !??

Hi all ! This works fine and returns a row of the result SELECT fd.fname, fd.lname, fd.role, fd.city, fd.cell, fd.email, fd.fid From fd WHERE fd.recruiter = 118 GROUP by fd.role and if I add Limit 1, 10 to this like this :- SELECT fd.fname, fd.lname, fd.role, fd.city, fd.cell, fd.email, fd.fid From fd WHERE fd.recruiter = 118 GROUP by fd.role LIMIT 1, 10 I get 0 rows. . What's going wrong here or is this like normal ?? Thanks all !

Hi Benanamen ! Glad that you are not upset. I have given the recruitment rules in the diagram above. Namely : -the Boss recruits the Admins & Masters. -the Admin recruits Masters & Franchisees, - the Masters recruit Franchisees. Each recruitment require that they be confirmed by all up the hierarchy. If a recruit has all the confirmations up the hierarchy then they they may be placed in their own tables as confirmed recruits. That's about it. Please ask for any specifics that come to your mind. Thanks.

Hi Benanamen, Thanks for the reply. I don't know why you are so upset. I complied with all that you said. I tried to provide all the information that I thought you would require. In fact my last mail clearly shows the rules and I provided that without you asking it because I realized late that you might require it so I sent it in a separate message. I also said that if you need to know anything specific then you could ask and I will try my best to provide it. It's OK, we can forget this. No need to be upset. Take care & thank you.

Hi benanamen ! I think by rules you mean the rules of recruitment. If that is what you were asking, they are as follows: BOSS / \ / \ Admin Master / \ \ Franch Master Franch | Franch Thank you.

Hi benanamen! I value all advice but it would help to know what makes you so sure that there is a design problem in this layout? In fact the layout of the picture is the blue print. Maybe you can elaborate and show me how you would build such a blueprint for a simple recruitee - recruit application. The rules are simple as I told you that it's hierarchical in nature. You can see them clearly in the layout as well as the sql dump. If there is something specific that you have in mind , then please ask and i'll try and answer my best. Thank you !

Hi benanamen, A picture is also worth a 1000 words & if it wasn't for this picture I would have no db !! Anyways here is the sql dump. Sorry for the delay - was really very sleepy last night. Its a hierarchical recruiter - recruit application. Hope this helps. Thank you. best.zip

Hi Benanamen, Sorry it was too late into the morning and i was very sleepy by then . I had no idea how to get the sql dump. I guess you meant that I should export the tables as sql files. Anyways please find the image of my tables below. That is exactly how my sql tables are designed. I made this before I made the tables so this gives an exact insight into the DB design. The tables are derived top to bottom. The staff table has the other fields like fname, lname, email etc. left for brevity. The design was suggested by Guru Jacques and so there is no doubt in my mind that it can be faulty. You can find that complete discussion in one of the questions on this forum. I'll attach a link to that question here shortly. I am not sure how you reached the conclusion that the DB design is faulty. Guru Barand did not say so. What I was trying to achieve with that query was to select all masters recruited by admins & all franchisees recruited by masters & all franchisees recruited by admins directly that have been registered. Hope this helps. Thank you.

Actually in thee tables Sir. However, It's just for clarity. So that I may be easily able to distinguish what the role of the person is in each of the tables, while I am developing this, without having to refer to the fd table each time. Once the queries are working, the role fields will be removed from the ar and the br tables. It would be present only in the fd table. Thank you.

Hi Guru Jacques, Sir thanks for pointing that out. That's just a typo while coping and pasting. Only one would be used and a few more columns from the fd table have to be included. The two roles are coming from two different tables and only one is required and sufficient ! If you think that it is otherwise all right, then I am ok with it. Thank you.

HI Guru Barand & Benanamen, A little bit of re-juggling on the query has seemingly achieved what I was trying to accomplish ! Here's what I did :- SELECT br.xid, br.role as role, 0 AS recruiter_id, 'Boss' as recruiter, br.activated as Status, fd.fname, fd.lname, fd.role, fd.email FROM br, fd WHERE br.xid = fd.fid UNION SELECT ar.xid, ar.role as role, ar.aid, 'Admin' as recruiter, (ar.conf_a + ar.conf_b) as status, fd.fname, fd.lname, fd.role, fd.email FROM ar, fd WHERE ar.xid = fd.fid UNION SELECT mr.xid, 'Franch' as role, mr.mid, 'Master' as recruiter, (mr.conf_a + mr.conf_m + mr.conf_a) as status, fd.fname, fd.lname, fd.role, fd.email FROM mr, fd WHERE mr.xid = fd.fid and finally got the output :- xid role recruiter_id recruiter Status fname lname role email 94 Admin 0 Boss 5 Parvin Kumar Admin arora@gmail.com 118 Admin 0 Boss 5 Sangita Sehar Admin sangita@fake.com 97 Franch 94 Admin 7 Jyoti Mehra Franch jyoti@fake.com 98 Master 94 Admin 7 Simmi Kumari Master simmi@fake.com 119 Master 118 Admin 2 Meenal Sodhi Master meenal@fake.com 117 Franch 98 Master -10 Rabia Kapoor Franch rabia@fake.com Thanks all ! your inputs and comments gave me ideas to rethink this and finally I think I managed to get what i wanted. Kind regards,

Right Guru Barand, I guess that's what is happening. SIr, is there a simple way to achieve what I am trying to do? That I get the required data for from the query for all the records in the UNION. I thought that removing the "Group by .." lines would solve it but, like I mentioned in the last post, it simply returns nothing. Please suggest an alternate way to achieve this if possible. Thank you.

Hi Guru Barand & Benanamen, Thanks for the replies and comments. @Guru Barand : Sir in the very first mail I have provided the combined output from of the bb and cc subquery, pasted below again for your convenience xid rid recruiter fname lname role city cell email status 94 0 Bo Parvin Kumar Ad Tarn Taran 9988878780 arora@gmail.com 5 97 94 Ad Jyoti Mehra Fr TarnTaran 9988875750 jyoti@fake.com 7 98 94 Ad Simmi Kumari Mas Tarn Taran 9988997750 simmi@fake.com 7 if I use a LEFTJOIN instead I of the INNER JOIN in the above query I get the following output:- xid rid recruiter fname lname role city cell email status 94 0 Bo Parvin Kumar Ad TaTara 9988878780 arora@gmail.com 5 118 0 Bo NULL NULL Ad NULL NULL NULL 5 97 94 Ad Jyoti Mehra Fr TaTara 9988875750 jyoti@fake.com 7 98 94 Ad Simmi Kumari Mas TaTara 9988997750 simmi@fake.com 7 119 118 Ad NULL NULL Mas NULL NULL NULL 2 117 98 Mas NULL NULL Fr NULL NULL NULL -10 If I use the bb sub-query by removing the GROUP By Line as follows :- SELECT fd.fid, fd.fname, fd.lname, fd.role, fd.city, fd.cell, fd.email From fd LIMIT 1, 10 The output that I get is :- fid fname lname role city cell email 94 Parvin Kumar Admin Tarn Taran 9988878780 arora@gmail.com 97 Jyoti Mehra Franchisee TarnTaran 9988875750 jyoti@fake.com 98 Simmi Kumari Master Tarn Taran 9988997750 simmi@fake.com 117 Rabia Kapoor Franchisee Tarntaran 9989878780 rabia@fake.com 118 SangeetaSehgal Admin Amritsar 9855143195 sangeeta@fake.com 119 Meenal Sodhi Master Amritsar 9914646666 meenal@fake.com If I don't remove the GROUP BY line, I get 0 rows as output. Surely as Kicken also suggested the GROUP by must is creating an issue here. If you can help me find a way around i'ld be grateful. I hope I have provided the output that you needed. Thank you. @Benanamen : Thanks for that caution. The design is a bit complicated I agree but its been thought of well. I implemented the scheme recommended by Guru Jacques in one of the previous mails. Any specific reason why UNIONS should not be used other than that they may be indicative of bad design. Thank you.

Hi ! This definitely has something to do with the GROUP BY clause as Kicken pointed out, but if I remove that the query executes to give an output of 0 - rows. The reson I get zero rows, I think, has something to do with status fields that are calculated. ( GURU BARAND in a previous query pointed that out ) The problem is that I want the data for each row of my union to display. How can I modify the query above to achieve that ??! Any help appreciated. Thanks all.

Hi kicken, Thanks for taking time out to reply. One set of data is the UNION table output below: xid role rid recruiter Status 94 Ad 0 Bo 5 118 Ad 0 Bo 5 97 Fr 94 Ad 7 98 Mas 94 Ad 7 119 Mas 118 Ad 2 117 Fr 98 Mas -10 The other data is from table fd. attached in my last reply. You can see that all the fid values are present in attached table fd are same as the xid values in the UNION. Since there are only 6 rows of data to display the limit is no issue. So the INNER JOIN should display data for all 6 rows in the UNION but it generates NULLs for 3 of those ! Sorry if I misunderstood something there. GROUP by should just group them by the role whether there are 6 or more rows. So I am still searching for the answer. !

Hi Guru Barand, Thank you very much for the reply. The thing is that the table fd has complete data for displaying the information for all 6 union records. I.e. fd has the fid, fname, lname, email, cell etc for all the 6 records generated by the union. xid role rid recruiter Status 94 Ad 0 Bo 5 118 Ad 0 Bo 5 97 Fr 94 Ad 7 98 Mas 94 Ad 7 119 Mas 118 Ad 2 117 Fr 98 Mas -10 yet the INNER JOIN displays only 3 records instead of all 6 records. The join is on fid = xid and for each xid there is an fid. In fact fid from table fd has generated xid for table ar. Please find attached a snapshot of the fd table with the data for these records:- So thats why it is puzzling. Maybe I am missing something?! Please help !

Hi all ! The following query :- SELECT br.xid, br.role as role, 0 AS rid, 'Bo' as recruiter, br.activated as Status FROM br UNION SELECT ar.xid, ar.role as role, ar.aid, 'Ad' as recruiter, (ar.confirmed_by_ad + ar.confirmed_by_bo) as status FROM ar UNION SELECT mr.xid, 'Fr' as role, mr.mid, 'Mas' as recruiter, (mr.confirmed_by_ad + mr.confirmed_by_mas + mr.confirmed_by_bo) as status FROM mr returns :- xid role rid recruiter Status 94 Ad 0 Bo 5 118 Ad 0 Bo 5 97 Fr 94 Ad 7 98 Mas 94 Ad 7 119 Mas 118 Ad 2 117 Fr 98 Mas -10 However when I combine the above union in a Join, SELECT cc.xid, cc.rid, cc.recruiter, bb.fname, bb.lname, cc.role, bb.city, bb.cell, bb.email, cc.status from ( SELECT br.xid, br.role as role, 0 AS rid, 'Bo' as recruiter, br.activated as Status FROM br UNION SELECT ar.xid, ar.role as role, ar.aid, 'Ad' as recruiter, (ar.confirmed_by_ad + ar.confirmed_by_bo) as status FROM ar UNION SELECT mr.xid, 'Fr' as role, mr.mid, 'Mas' as recruiter, (mr.confirmed_by_ad + mr.confirmed_by_mas + mr.confirmed_by_bo) as status FROM mr )cc INNER JOIN ( SELECT fd.fname, fd.lname, fd.role, fd.fid, fd.city, fd.cell, fd.email FROM fd GROUP by fd.role LIMIT 1, 10 ) bb ON bb.fid = cc.xid I get the output as :- xid rid recruiter fname lname role city cell email status 94 0 Bo Parvin Kumar Ad Tarn Taran 9988878780 arora@gmail.com 5 97 94 Ad Jyoti Mehra Fr TarnTaran 9988875750 jyoti@fake.com 7 98 94 Ad Simmi Kumari Mas Tarn Taran 9988997750 simmi@fake.com 7 if I use a LEFTJOIN instead I of the INNER JOIN in the above query I get the following output:- xid rid recruiter fname lname role city cell email status 94 0 Bo Parvin Kumar Ad TaTara 9988878780 arora@gmail.com 5 118 0 Bo NULL NULL Ad NULL NULL NULL 5 97 94 Ad Jyoti Mehra Fr TaTara 9988875750 jyoti@fake.com 7 98 94 Ad Simmi Kumari Mas TaTara 9988997750 simmi@fake.com 7 119 118 Ad NULL NULL Mas NULL NULL NULL 2 117 98 Mas NULL NULL Fr NULL NULL NULL -10 The question is that why is the INNER join giving the correct result for 3 records and NULLs for the rest ???? I have not used any special condition in the joins. If the unions return 6 results ( 1st output table above ) then why does the INNER JOIN return the NULLS for 3 and corrcet information for the other 3 ??? Stumped. Please help ! Thanks all !

Guru Barand !!!!!!!! Thank you !

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 !