ajoo Posted August 24, 2016 Share Posted August 24, 2016 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 ! Quote Link to comment Share on other sites More sharing options...
Barand Posted August 24, 2016 Share Posted August 24, 2016 That is way the two types of join are meant to work. A INNER JOIN B returns only rows where there is a match on the join condition in both A and B A LEFT JOIN B returns all rows from A with any matching data from B. If there is no matching B row then columns from B contain NULL Quote Link to comment Share on other sites More sharing options...
ajoo Posted August 24, 2016 Author Share Posted August 24, 2016 (edited) 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 ! Edited August 24, 2016 by ajoo Quote Link to comment Share on other sites More sharing options...
kicken Posted August 24, 2016 Share Posted August 24, 2016 SELECT fd.fname, fd.lname, fd.role, fd.fid, fd.city, fd.cell, fd.email FROM fd GROUP by fd.role LIMIT 1, 10The group by and limit clauses mean you'll only get at most 10 rows from that query, 1 for each fd.role. Probably given that subset of data there is not always a fid match. Quote Link to comment Share on other sites More sharing options...
ajoo Posted August 24, 2016 Author Share Posted August 24, 2016 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. ! Quote Link to comment Share on other sites More sharing options...
ajoo Posted August 24, 2016 Author Share Posted August 24, 2016 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 24, 2016 Share Posted August 24, 2016 What is the output from the bb subquery? You have only shown us the cc subquery half of the join. Quote Link to comment Share on other sites More sharing options...
benanamen Posted August 24, 2016 Share Posted August 24, 2016 When I see someone using UNION that waves a red flag to me about the DB design. @Barand, with the info OP has provided, wouldnt you say there is an underlying DB design problem here? Quote Link to comment Share on other sites More sharing options...
Barand Posted August 24, 2016 Share Posted August 24, 2016 ... wouldnt you say there is an underlying DB design problem here? Without knowing the structures of the three tables I couldn't comment. If the three structures are identical then that would flag a problem. The presence of a UNION keyword is insufficient evidence on its own. There are valid reasons for using UNION - that's why it exists. Consider a college scenario. Two tables, pupil and lecturer. In the event of a fire in one of the buildings, the fire department asks for a list of names of who is scheduled to be in the building. There is going to be union in there somewhere. Quote Link to comment Share on other sites More sharing options...
benanamen Posted August 24, 2016 Share Posted August 24, 2016 (edited) Consider a college scenario. Two tables, pupil and lecturer. In that particular example I would say that should be one table people. A lecturer could also be a pupil and vise versa. You would end up duplicating data with two tables. Edited August 24, 2016 by benanamen Quote Link to comment Share on other sites More sharing options...
ajoo Posted August 24, 2016 Author Share Posted August 24, 2016 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 24, 2016 Share Posted August 24, 2016 GROUP BY role will give you a single row for each role. As you have two rows in each role, that single grouped role record can only contain one of the ids (usually the first, but not guaranteed to be). This will explain why you only get matches on ids 94, 97, 98 when the group by is used in the second subquery Quote Link to comment Share on other sites More sharing options...
benanamen Posted August 24, 2016 Share Posted August 24, 2016 (edited) @ajoo, best thing to do is post an actual SQL dump of your DB schema along with sample data. The database is the foundation of all the code you are going to write so it is very important that the DB is correct FIRST. There are reasons to use UNION as Barand has said, but when it comes to noobs on forums using it, they database is usually designed incorrectly. Edited August 24, 2016 by benanamen Quote Link to comment Share on other sites More sharing options...
ajoo Posted August 24, 2016 Author Share Posted August 24, 2016 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 24, 2016 Share Posted August 24, 2016 is there a simple way to achieve what I am trying to do? Probably, but we do not know what you are trying to do. We only know your attempted solution, which is failing. @ajoo, best thing to do is post an actual SQL dump of your DB schema along with sample data. And, as benanamen requested, we don't have your data Quote Link to comment Share on other sites More sharing options...
ajoo Posted August 24, 2016 Author Share Posted August 24, 2016 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, Quote Link to comment Share on other sites More sharing options...
Barand Posted August 24, 2016 Share Posted August 24, 2016 Are you really sure that is what you wanted. You have two "role" columns which have the same value in the output. I suspect they had different input values before the joins. Change the alias name. Quote Link to comment Share on other sites More sharing options...
ajoo Posted August 24, 2016 Author Share Posted August 24, 2016 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 24, 2016 Share Posted August 24, 2016 That raises the question "Why are you storing the role in two different places?" Quote Link to comment Share on other sites More sharing options...
ajoo Posted August 24, 2016 Author Share Posted August 24, 2016 (edited) 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. Edited August 24, 2016 by ajoo Quote Link to comment Share on other sites More sharing options...
benanamen Posted August 24, 2016 Share Posted August 24, 2016 It's pretty clear that your database design is completely wrong. Unless you fix it correctly, everything you do with the database will be some sort of hack. You were offered to have experts review your database design at no charge yet you don't take advantage of it. I don't understand that. If you go into coding with a "it works so I'll use it" attitude you're not going to get very far. Quote Link to comment Share on other sites More sharing options...
ajoo Posted August 25, 2016 Author Share Posted August 25, 2016 (edited) 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. Edited August 25, 2016 by ajoo Quote Link to comment Share on other sites More sharing options...
benanamen Posted August 25, 2016 Share Posted August 25, 2016 (edited) A picture is for hanging on the wall. Please post the actual SQL dump. I have no idea what all the pretty colors mean and cannot see how tables are tied together. I can't import a picture to my database and recreate your schema and I cant run queries against a picture. It will be helpful to have an overview of what this app is. Edited August 25, 2016 by benanamen Quote Link to comment Share on other sites More sharing options...
ajoo Posted August 25, 2016 Author Share Posted August 25, 2016 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 Quote Link to comment Share on other sites More sharing options...
benanamen Posted August 25, 2016 Share Posted August 25, 2016 I did study the picture for a little bit and it is clear you definitely have design issues even without knowing what it does. From your description it is apparent we need to define exactly what this app is going to do and the rules regarding that before a single table is created. So if you have not done so already, put together a detailed requirement list for this app including a much more detailed description of the app. Think in terms of a blueprint for a house. With a proper blueprint any Builder could build the house without asking questions. 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.