June 14, 2015 at 8:11 am
I have a right join query where i have a table where all employees attendence entry are registered and another table where pay master table.
I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.
My query
SELECT TOP (100) PERCENT MHD_MONTH_SHEET.Calender_Date AS Expr2, Pay_Emp_Master.Emp_Code AS Expr3, Pay_Emp_Master.Dept_Code AS Expr4,
Pay_Emp_Master.Emp_First_Name, MHD_MONTH_SHEET.Emp_Name AS Expr5, MHD_MONTH_SHEET.Dept_Code AS Dept
FROM MHD_MONTH_SHEET RIGHT OUTER JOIN
Pay_Emp_Master ON MHD_MONTH_SHEET.Emp_Code = Pay_Emp_Master.Emp_Code AND MHD_MONTH_SHEET.Emp_Code <> Pay_Emp_Master.Emp_Code
It gives NULL values to all my pay master table values
June 14, 2015 at 12:29 pm
I do not know if I understand correctly but try:
SELECT
MHD_MONTH_SHEET.Calender_Date AS Expr2,
Pay_Emp_Master.Emp_Code AS Expr3,
Pay_Emp_Master.Dept_Code AS Expr4,
Pay_Emp_Master.Emp_First_Name,
MHD_MONTH_SHEET.Emp_Name AS Expr5,
MHD_MONTH_SHEET.Dept_Code AS Dept
FROM MHD_MONTH_SHEET
LEFT OUTER JOIN Pay_Emp_Master
ON MHD_MONTH_SHEET.Emp_Code = Pay_Emp_Master.Emp_Code
If you do not work as you want, I think it's better you post an example with a sample of data in tables and their expected result.
Hope this helps.
June 14, 2015 at 4:23 pm
Your join condition will never evaluate TRUE and that's why you're always getting NULLs.
June 15, 2015 at 6:54 am
syed_3177 (6/14/2015)
I have a right join query where i have a table where all employees attendence entry are registered and another table where pay master table.I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.
My query
SELECT TOP (100) PERCENT MHD_MONTH_SHEET.Calender_Date AS Expr2, Pay_Emp_Master.Emp_Code AS Expr3, Pay_Emp_Master.Dept_Code AS Expr4,
Pay_Emp_Master.Emp_First_Name, MHD_MONTH_SHEET.Emp_Name AS Expr5, MHD_MONTH_SHEET.Dept_Code AS Dept
FROM MHD_MONTH_SHEET RIGHT OUTER JOIN
Pay_Emp_Master ON MHD_MONTH_SHEET.Emp_Code = Pay_Emp_Master.Emp_Code AND MHD_MONTH_SHEET.Emp_Code <> Pay_Emp_Master.Emp_Code
It gives NULL values to all my pay master table values
As previously indicated, the reason for the NULL values is that your JOIN condition specifies that the two Emp_Code fields have to be both equal as well as unequal in order for the join to occur. Also, just as a general rule, almost no one uses RIGHT joins because it's just not intuitive. There's a functional equivalent to your query using LEFT OUTER JOIN, and a previous poster provided one. Finally, you really should name your fields something other than Expr1, Expr2, etc... Here's an updated query for your perusal. Let us know what works for you.
SELECT MS.Calender_Date,
PM.Emp_Code,
PM.Dept_Code,
PM.Emp_First_Name,
MS.Emp_Name AS MS_Emp_Name,
MS.Dept_Code AS Dept
FROM Pay_Emp_Master AS PM
LEFT OUTER JOIN MHD_MONTH_SHEET AS MS
ON PM.Emp_Code = MS.Emp_Code
P.S. I took out the TOP (100) PERCENT because you don't appear to need it - you don't have an ORDER BY clause.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 15, 2015 at 7:45 am
sgmunson (6/15/2015)Also, just as a general rule, almost no one uses RIGHT joins because it's just not intuitive.
I've actually wondered about this. I know this is true of left-to-right languages like English, but RIGHT joins might be more intuitive for speakers of languages that are written right-to-left like Hebrew or Arabic. Since, I'm not a native speaker of any of these languages, I don't have any insight into whether this is true.
Of course, since SQL is based on English, the fact that English is written left-to-right might override the influence of languages written right-to-left.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 15, 2015 at 9:17 am
drew.allen (6/15/2015)
sgmunson (6/15/2015)Also, just as a general rule, almost no one uses RIGHT joins because it's just not intuitive.
I've actually wondered about this. I know this is true of left-to-right languages like English, but RIGHT joins might be more intuitive for speakers of languages that are written right-to-left like Hebrew or Arabic. Since, I'm not a native speaker of any of these languages, I don't have any insight into whether this is true.
Of course, since SQL is based on English, the fact that English is written left-to-right might override the influence of languages written right-to-left.
Drew
I guess I didn't even think about alternate language reading order, but I'm still going to suggest that giving credence to the RIGHT joins is still probably a bad idea, as it will just confuse the daylights out of most folks. And yes, with SQL being written in English, presenting the tables in LEFT to RIGHT order is going to make it a LOT EASIER to read, and more importantly, to understand.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 15, 2015 at 8:31 pm
sgmunson (6/15/2015)
drew.allen (6/15/2015)
sgmunson (6/15/2015)Also, just as a general rule, almost no one uses RIGHT joins because it's just not intuitive.
I've actually wondered about this. I know this is true of left-to-right languages like English, but RIGHT joins might be more intuitive for speakers of languages that are written right-to-left like Hebrew or Arabic. Since, I'm not a native speaker of any of these languages, I don't have any insight into whether this is true.
Of course, since SQL is based on English, the fact that English is written left-to-right might override the influence of languages written right-to-left.
Drew
I guess I didn't even think about alternate language reading order, but I'm still going to suggest that giving credence to the RIGHT joins is still probably a bad idea, as it will just confuse the daylights out of most folks. And yes, with SQL being written in English, presenting the tables in LEFT to RIGHT order is going to make it a LOT EASIER to read, and more importantly, to understand.
I don't know about this. I personally have never had a difficult time understanding RIGHT joins and think there are places where they make more sense. I use FULL joins a lot too which confuses people but is the most elegant solution given the requirement. I'm not going to chose a less elegant solution because some developers will be confused, I will just add better comments and make myself available for questions.
-- Itzik Ben-Gan 2001
June 16, 2015 at 5:23 am
Alan.B (6/15/2015)
sgmunson (6/15/2015)
drew.allen (6/15/2015)
sgmunson (6/15/2015)Also, just as a general rule, almost no one uses RIGHT joins because it's just not intuitive.
I've actually wondered about this. I know this is true of left-to-right languages like English, but RIGHT joins might be more intuitive for speakers of languages that are written right-to-left like Hebrew or Arabic. Since, I'm not a native speaker of any of these languages, I don't have any insight into whether this is true.
Of course, since SQL is based on English, the fact that English is written left-to-right might override the influence of languages written right-to-left.
Drew
I guess I didn't even think about alternate language reading order, but I'm still going to suggest that giving credence to the RIGHT joins is still probably a bad idea, as it will just confuse the daylights out of most folks. And yes, with SQL being written in English, presenting the tables in LEFT to RIGHT order is going to make it a LOT EASIER to read, and more importantly, to understand.
I don't know about this. I personally have never had a difficult time understanding RIGHT joins and think there are places where they make more sense. I use FULL joins a lot too which confuses people but is the most elegant solution given the requirement. I'm not going to chose a less elegant solution because some developers will be confused, I will just add better comments and make myself available for questions.
Part of why I say that is because I'm often a consultant that has to develop a solution pronto and there is no time to document nor to explain the reasoning. As there is always a way to represent a RIGHT JOIN with an equivalent LEFT JOIN, and avoiding anything that might even appear to be "rocket science" is too valuable when one is not going to be around for questions because your contract will end, the value of simplicity is too high to ignore.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 16, 2015 at 7:16 am
sgmunson (6/15/2015)
syed_3177 (6/14/2015)
I have a right join query where i have a table where all employees attendence entry are registered and another table where pay master table.I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.
My query
SELECT TOP (100) PERCENT MHD_MONTH_SHEET.Calender_Date AS Expr2, Pay_Emp_Master.Emp_Code AS Expr3, Pay_Emp_Master.Dept_Code AS Expr4,
Pay_Emp_Master.Emp_First_Name, MHD_MONTH_SHEET.Emp_Name AS Expr5, MHD_MONTH_SHEET.Dept_Code AS Dept
FROM MHD_MONTH_SHEET RIGHT OUTER JOIN
Pay_Emp_Master ON MHD_MONTH_SHEET.Emp_Code = Pay_Emp_Master.Emp_Code AND MHD_MONTH_SHEET.Emp_Code <> Pay_Emp_Master.Emp_Code
It gives NULL values to all my pay master table values
As previously indicated, the reason for the NULL values is that your JOIN condition specifies that the two Emp_Code fields have to be both equal as well as unequal in order for the join to occur. Also, just as a general rule, almost no one uses RIGHT joins because it's just not intuitive.
I agree that putting AND MHD_MONTH_SHEET.Emp_Code <> Pay_Emp_Master.Emp_Code in the join condition is pure nonsense and should be eliminated (as should the meaningless TOP (100) PERCENT).
But the reason most people don't use right joins is that some people who were too lazy to think clearly (and maybe those not too lazy, but hard-working people incapable of thinking clearly) acting together with people too lazy to learn to handle both left and right joins created the myth that right joins are not intuitive, and this pernicious nonsense has spread so successfully (like several other awful SQL myths) that the majority of people writing SQL now believe it. Personally, I have difficulty understanding how anyone can understand left joins and not understand right joins, and find it extremely hard to believe that someone who doesn't understand right joins can possibly understand full joins.
Tom
June 16, 2015 at 8:11 am
TomThomson (6/16/2015)
sgmunson (6/15/2015)
syed_3177 (6/14/2015)
I have a right join query where i have a table where all employees attendence entry are registered and another table where pay master table.I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.
My query
SELECT TOP (100) PERCENT MHD_MONTH_SHEET.Calender_Date AS Expr2, Pay_Emp_Master.Emp_Code AS Expr3, Pay_Emp_Master.Dept_Code AS Expr4,
Pay_Emp_Master.Emp_First_Name, MHD_MONTH_SHEET.Emp_Name AS Expr5, MHD_MONTH_SHEET.Dept_Code AS Dept
FROM MHD_MONTH_SHEET RIGHT OUTER JOIN
Pay_Emp_Master ON MHD_MONTH_SHEET.Emp_Code = Pay_Emp_Master.Emp_Code AND MHD_MONTH_SHEET.Emp_Code <> Pay_Emp_Master.Emp_Code
It gives NULL values to all my pay master table values
As previously indicated, the reason for the NULL values is that your JOIN condition specifies that the two Emp_Code fields have to be both equal as well as unequal in order for the join to occur. Also, just as a general rule, almost no one uses RIGHT joins because it's just not intuitive.
I agree that putting AND MHD_MONTH_SHEET.Emp_Code <> Pay_Emp_Master.Emp_Code in the join condition is pure nonsense and should be eliminated (as should the meaningless TOP (100) PERCENT).
But the reason most people don't use right joins is that some people who were too lazy to think clearly (and maybe those not too lazy, but hard-working people incapable of thinking clearly) acting together with people too lazy to learn to handle both left and right joins created the myth that right joins are not intuitive, and this pernicious nonsense has spread so successfully (like several other awful SQL myths) that the majority of people writing SQL now believe it. Personally, I have difficulty understanding how anyone can understand left joins and not understand right joins, and find it extremely hard to believe that someone who doesn't understand right joins can possibly understand full joins.
Quite - assuming that the common pattern of all LEFT JOINs or all RIGHT JOINs is followed. Mix RIGHT and LEFT joins in the same query with multiple predicates and it quickly becomes very difficult to resolve.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2015 at 9:10 am
TomThomson (6/16/2015)
sgmunson (6/15/2015)
syed_3177 (6/14/2015)
I have a right join query where i have a table where all employees attendence entry are registered and another table where pay master table.I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.
My query
SELECT TOP (100) PERCENT MHD_MONTH_SHEET.Calender_Date AS Expr2, Pay_Emp_Master.Emp_Code AS Expr3, Pay_Emp_Master.Dept_Code AS Expr4,
Pay_Emp_Master.Emp_First_Name, MHD_MONTH_SHEET.Emp_Name AS Expr5, MHD_MONTH_SHEET.Dept_Code AS Dept
FROM MHD_MONTH_SHEET RIGHT OUTER JOIN
Pay_Emp_Master ON MHD_MONTH_SHEET.Emp_Code = Pay_Emp_Master.Emp_Code AND MHD_MONTH_SHEET.Emp_Code <> Pay_Emp_Master.Emp_Code
It gives NULL values to all my pay master table values
As previously indicated, the reason for the NULL values is that your JOIN condition specifies that the two Emp_Code fields have to be both equal as well as unequal in order for the join to occur. Also, just as a general rule, almost no one uses RIGHT joins because it's just not intuitive.
I agree that putting AND MHD_MONTH_SHEET.Emp_Code <> Pay_Emp_Master.Emp_Code in the join condition is pure nonsense and should be eliminated (as should the meaningless TOP (100) PERCENT).
But the reason most people don't use right joins is that some people who were too lazy to think clearly (and maybe those not too lazy, but hard-working people incapable of thinking clearly) acting together with people too lazy to learn to handle both left and right joins created the myth that right joins are not intuitive, and this pernicious nonsense has spread so successfully (like several other awful SQL myths) that the majority of people writing SQL now believe it. Personally, I have difficulty understanding how anyone can understand left joins and not understand right joins, and find it extremely hard to believe that someone who doesn't understand right joins can possibly understand full joins.
I agree that understanding a RIGHT join is no harder than understanding a LEFT one, but once you let that cat out of the bag, along comes the temptation to mix the two, and that always ends badly. There are also a number of automated systems that generate views that are particularly difficult to really understand precisely because they mix in both varieties and then insist on making join order important by putting two or more joins prior to an ON clause. Try and support such a mess, or add another join, and you can spend hours just trying to understand what it already does, never mind make changes. Having just one point of view for queries, makes it a LOT easier to deal with, and that's not laziness, it's just common sense. Just because one person with advanced skill can understand it all easily, doesn't mean the person following you will be able to do so. I get judged far more on how maintainable my code is than on how "pretty" I can make it - with having it do the job correctly and with good performance being the "most important" element.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply