Query woes :confused:

[ajoo]

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 !

 

 

 

 

[Barand]

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

[ajoo]

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

[kicken]

SELECT fd.fname, fd.lname, fd.role, fd.fid, fd.city, fd.cell, fd.email

FROM fd 

GROUP by fd.role

LIMIT 1, 10

The 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.

[ajoo]

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. !

[ajoo]

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. 

[Barand]

What is the output from the bb subquery? You have only shown us the cc subquery half of the join.

[benanamen]

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?

[Barand]

... 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.

[benanamen]

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

[ajoo]

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.

[Barand]

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

[benanamen]

@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

[ajoo]

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.

[Barand]

 

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

[ajoo]

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,

[Barand]

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.

[ajoo]

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. 

[Barand]

That raises the question "Why are you storing the role in two different places?"

[ajoo]

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

[benanamen]

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.

[ajoo]

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

[benanamen]

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

[ajoo]

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

[benanamen]

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.