ajoo

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 !

Hi Psycho, Yes you were right. Also the temporary table does not show in the phpadmin tables. However queries excuted against the tmp table work !! So the table is created and somewhere there but not visible !! Thank you.

Hi Psycho ! Thank you for the response. Yes I did think about that but failed to see the temporary table tmp_result in the tables in phpmyadmin. So maybe it doesn't show out there at all ! I'll run a query against it anyways and revert. Thanks you.

hmmm even this works and displays the result set correctly. SELECT st_id from (( SELECT mr.st_id as st_id from mr,ar where mr.mid = ar.st_id and ar.aid = 1) UNION (SELECT ar.st_id as st_id from ar WHERE ar.aid = 1 && ar.role <> 'master')) tt It's only the CREATE TEMPORARY TABLE ... bit that returns an empty result set ( zero rows ).

Hi all ! while this query (with table names shortened for clarity) works great and returns a result set just fine, SELECT mr.st_id as st_id from mr,ar where mr.mid = ar.st_id and ar.aid = 1 UNION SELECT ar.st_id as st_id from ar WHERE ar.aid = 1 && ar.role <> 'master' this following one executes and returns a zero result set. CREATE TEMPORARY TABLE tmp_result SELECT st_id from (( SELECT mr.st_id as st_id from mr,ar where mr.mid = ar.st_id and ar.aid = 1) UNION (SELECT ar.st_id as st_id from ar WHERE ar.aid = 1 && ar.role <> 'master')) tt I wish to use the result set obtained from the first query to join with another query a few lines away. Please suggest what is going wrong here & if this approach is fine and workable. Thanks all !

Hi, I think you are getting it wrong still. Guru Jacques suggested that you add a single table containing all the skills and associate users to those skills.

Hi Kicken, Thanks for the reply. I guess I need more practice before I would be able to figure out which tables require to be split and which not. Database normalization and the desire to do things right have me all confused. I will keep in mind the two points that you have enlisted above before I decide on splitting a table next time. Regarding the other example, it is similar & so reinforces what was proposed by Guru Jacques. I am also using a common uid (as in the three of your tables) from the user_detail table for the different types of users in my tables. Thank you very much.

Hi Guru Jacques, Why am i splitting the database !? Good question sir !! Just because I thought that it could be segmented further and normalized. you know like separating the address from the contacts and so on. Do you think I need not split it !? is it normalized as it is and needs no improvement? I'll be happy to let it be !! I can start working on the tables you suggested. Thank you.

Hi Guru Jacques, I have the following table user_detail(uid (PK, AI), reg_date, role, fname, lname, dob, age, gender, fathername, spouse, add1, add2, city, state, country, zip, email, cell, phone, user_status) I was using a single query to insert these values in the table as follows $query = "INSERT into user_detail(reg_date, role, fname, lname, dob, age, gender, fathername, spouse, add1, add2, city, state, country, zip, email, cell, phone, user_status) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; Now I am splitting the table user_detail as follows: user_detail(uid(PK) ,fname,lname,dob,reg_date) user_add(uid(FK), house_no, add_street, add_landmark, street,city, zip, state) user_contact(uid(FK), email, phone, cell) Since all these values are being spewed by a single form and must be stored simultaneously would i not have to use a transactional query for inserting values into the 3 different tables? So I think this is the first example of a multiple insert that I was talking about in the earlier message. So now I think that a transactional query is the best way to go about inserting the data in these tables. Or is there another easier way to form an insert query to accomplish this? Thank you very much !

Hi Funster and Guru Jacques, Thanks for taking out time to reply, I asked that generally, but once I get implementing this, I'll get back with any examples that I come across. Those are two lines for the #xxx street and maybe landmark. A person can use one or both. Maybe I should use just one text field for that. Should the the field approved_by_manager In the AM table, and the fields approving_assistent_manager_id, approved_by_manager in the DM table be the actual AM_ID and DM_ID from their respective AM, DM tables or should they be like a 0,1 flag fields If they should be the AM_ID and DM_ID respectively( which is what I feel they are) shouldn't there be another field like recruited (a recruitment status field) which is a 0,1 flag field indicating that person is recruited or not ? Thanks again Funster and Guru Jacques.