RIGHT-JOIN table not fetching all rows in WHERE clause
-
I'm trying to add an additional table (hrxoreturn) with RIGHT JOIN to return all records either with matching or no matched records to other tables. When i exclude "count(distinct hrxoissue.issuedte)as 'Total Count per RX'" , the fetched rows goes to 49 rows reading all the count in hrxoreturn table, but if i include count on hrxoissue.issuedte, the fetched rows only goes to 40 rows. Any ideas? do i need to do a sub-query?
Here's the screenshot:
excluding the count(distinct hrxoissue.issuedte)as 'Total Count per RX
SELECT concat(hgen.gendesc , ',' , (CASE WHEN hdmhdr.brandname IS NULL THEN '' ELSE CONCAT(' ' , hdmhdr.brandname) END) ,
(CASE WHEN hdmhdr.dmdnost IS NULL THEN '' ELSE CONCAT(' ' , CONVERT(hdmhdr.dmdnost,NCHAR(14))) END) ,
(CASE WHEN hstre.stredesc IS NULL THEN '' ELSE hstre.stredesc END) ,
(CASE WHEN hform.formdesc IS NULL THEN '' ELSE CONCAT(' ' , hform.formdesc) END) ,
(CASE WHEN hroute.rtedesc IS NULL THEN '' ELSE CONCAT(' ' , hroute.rtedesc) END)) AS 'itemdesc',
count(distinct hrxoissue.issuedte)as 'Total Count per RX', sum(hrxoissue.qty) as 'Quantity', count(distinct hrxoreturn.returndate) as 'Total Count per CM', hrxoissue.dmdcomb, hcharge.chrgdesc
from hdmhdr
LEFT JOIN hroute ON hdmhdr.rtecode = hroute.rtecode
LEFT JOIN hstre ON hdmhdr.strecode = hstre.strecode
INNER JOIN hdruggrp ON hdruggrp.grpcode = hdmhdr.grpcode
INNER JOIN hform ON hform.formcode = hdmhdr.formcode
INNER JOIN hgen ON hgen.gencode = hdruggrp.gencode
INNER JOIN hrxoissue ON hdmhdr.dmdcomb = hrxoissue.dmdcomb
INNER JOIN hcharge ON hrxoissue.chrgcode = hcharge.chrgcode
RIGHT JOIN hrxoreturn ON hdmhdr.enccode = hrxoreturn.enccode
where hdmhdr.dmdstat = 'A' AND (hrxoissue.issuedte >= '2023-02-14 00:00:00' and hrxoissue.issuedte <= '2023-02-14 23:59:00')
AND (hrxoreturn.returndate >= '2023-02-14 00:00:00' and hrxoreturn.returndate <= '2023-02-14 23:59:00')
group by itemdesc; -
This line in the WHERE clause
AND (hrxoreturn.returndate >= '2023-02-14 00:00:00' and hrxoreturn.returndate <= '2023-02-14 23:59:00')
changes your RIGHT JOIN to an INNER JOIN. If you move the line to your JOIN conditions, you should get the results you're expecting.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I can't say I have ever used RIGHT JOIN. I think it is only in the language for completeness.
- Jonathan AC Roberts wrote:
I can't say I have ever used RIGHT JOIN. I think it is only in the language for completeness.
I use it a fair bit in real life.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Jonathan AC Roberts wrote:
I can't say I have ever used RIGHT JOIN. I think it is only in the language for completeness.
I use it a fair bit in real life.
Why would you use a RIGHT JOIN instead of 'inverting it' and using a LEFT JOIN*. The LEFT JOIN is much more familiar to most users of SQL Server.
*I now expect my mind to be blown and my understanding of SQL Server to fundamentally shift. Which is what usually happens when Jeff explains why he does something.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
- Neil Burton wrote:Jeff Moden wrote:Jonathan AC Roberts wrote:
I can't say I have ever used RIGHT JOIN. I think it is only in the language for completeness.
I use it a fair bit in real life.
Why would you use a RIGHT JOIN instead of 'inverting it' and using a LEFT JOIN*. The LEFT JOIN is much more familiar to most users of SQL Server.
*I now expect my mind to be blown and my understanding of SQL Server to fundamentally shift. Which is what usually happens when Jeff explains why he does something.
Thank you for the compliment. Nothing here will blow your mind though.
It's a matter of what I have on my mind while writing the code. Like a lot of people, my tendency is to list the "hub" table first and that normally results in Left Outer Joins when outer joins are needed. There are times though, where I have what I can only refer to as a "bird query" that contains a different thought process or thought train. I you look at a bird flying head on, the "hub" table is in the middle and the "left wing" would be from Right Outer Joins and the right wing would be Left Outer joins to the hub.
I sometimes use them separate legs on a "skip join" (one leg has a direct connection between tables "A" and "C" and the other leg has an intermediate table of "A", "B", and "C".
I can't speak for others but, when I look at such a construction in the FROM clause, it's just easier for me especially if I've drawn and ERD to support the query (or whatever). It could just as easily be done with all Left Joins and I wouldn't fault anyone for doing so. For me, it simply depends on my immediate thought process and, sometimes, what my blood levels are for caffeine. 😀
The bottom line for me is how I'm visualizing the Joins in the ERD I'm seeing in my mind. I just "go with the flow" there because I got tired of losing arguments with myself. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Unless I am missing something - the problem is this: hdmhdr.dmdstat = 'A'
The right join is also joining to this table - and in a right join it is the second table in the statement that will be preserved.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
There's a folk saying in the standards world that "the committee never met a feature he didn't like." And that's very true in the case of the joints in SQL. You're absolutely correct the right outer join, corresponding, natural join, and several other exotic things that nobody really uses were added for completeness. Once we had a simple way of defining them, the standard committee went wild. The current LEFT OUTER JOIN syntax replaced the old *=, OUTER, and += operators from various vendors. In particular, in the Sybase/SQL Server world,*= doesn't quite behave the same way as the current syntax.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply