Select Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Query

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-05-12 : 11:33:30

Hi,

I have the following query which works fine. However, when dbo.consultationData_566_Pat.MINEventdate is NULL the record is not displayed at the output. I think the problem is in the criteria highlighted in blue.

I would like to display the record also when the MINEventdate is NULL.

Any help please..........Thank you

SELECT     TOP (100) PERCENT dbo.NonCurrentPat_566.patid, dbo.NonCurrentPat_566.yob + 1800 AS YrBirth, dbo.consultationData_566_Pat.MINEventdate
FROM         dbo.NonCurrentPat_566 INNER JOIN
                      dbo.consultationData_566 ON dbo.NonCurrentPat_566.patid = dbo.consultationData_566.patid AND 
                      dbo.NonCurrentPat_566.yob + 1800 > YEAR(dbo.consultationData_566.eventdate) INNER JOIN
                      dbo.consultationData_566_Pat ON dbo.consultationData_566.patid = dbo.consultationData_566_Pat.patid
GROUP BY dbo.NonCurrentPat_566.patid, dbo.NonCurrentPat_566.yob + 1800, dbo.consultationData_566_Pat.patid, dbo.consultationData_566_Pat.MINEventdate
ORDER BY dbo.NonCurrentPat_566.patid

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-12 : 12:12:03

I doubt if it is the dbo.consultationData_566_Pat.MINEventdate being null that is causing the problem. It is not anywhere in the join conditions or where clauses. More likely that you don't have a row in the dbo.consultationData_566_Pat table. You can test that theory by changing the second inner join to a left outer join as in


   INNER  LEFT JOIN dbo.consultationData_566_Pat
      ON  dbo.consultationData_566.patid = dbo.consultationData_566_Pat.patid

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-12 : 12:14:26

Let's start with the easy fix
SELECT TOP (100) PERCENT dbo.NonCurrentPat_566.patid, dbo.NonCurrentPat_566.yob + 1800 AS YrBirth
, dbo.consultationData_566_Pat.MINEventdate
FROM dbo.NonCurrentPat_566
INNER JOIN dbo.consultationData_566 ON
dbo.NonCurrentPat_566.patid = dbo.consultationData_566.patid
AND dbo.NonCurrentPat_566.yob + 1800 > YEAR(dbo.consultationData_566.eventdate)
LEFT JOIN dbo.consultationData_566_Pat ON
dbo.consultationData_566.patid = dbo.consultationData_566_Pat.patid
and dbo.consultationData_566_Pat.MINEventdate is not null
GROUP BY dbo.NonCurrentPat_566.patid, dbo.NonCurrentPat_566.yob + 1800
, dbo.consultationData_566_Pat.patid, dbo.consultationData_566_Pat.MINEventdate
ORDER BY dbo.NonCurrentPat_566.patid
Jim

Everyday I learn something that somebody else already knew

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-05-12 : 12:19:21

It didnt work..

I am expecting result as

patid YrBirth MINEventDate
1 1975 18/06/1970
2 1976 23/08/1940
3 1980 NULL
4 1981 NULL

But I get only

patid YrBirth MINEventDate
1 1975 18/06/1970
2 1976 23/08/1940

I hope am clearer, thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-12 : 12:20:15

Neither worked?

Jim

Everyday I learn something that somebody else already knew

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-05-12 : 12:22:41

Jim it gave same results as shown in previous thread.. Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-12 : 12:30:49

Jim, that

was for webfred!!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=160540

dr223, do the following:

First, make sure that all four rows are in dbo.consultationData_566.
Then, change the other inner join to a left join and see if that gives you the result you want.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-12 : 12:37:09

Looks like I'll be sleeping with one eye open!

Jim

Everyday I learn something that somebody else already knew

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-05-13 : 05:05:18

Hi,

Changed the code to

SELECT TOP (100) PERCENT dbo.NonCurrentPat_566.patid, dbo.NonCurrentPat_566.yob + 1800 AS YrBirth, dbo.consultationData_566_Pat.MINeventdate,
dbo.ConsultationData_566.eventdate
FROM dbo.NonCurrentPat_566 INNER JOIN
dbo.ConsultationData_566 ON dbo.NonCurrentPat_566.patid = dbo.ConsultationData_566.patid AND
dbo.NonCurrentPat_566.yob + 1800 > YEAR(dbo.ConsultationData_566.eventdate) LEFT OUTER JOIN
dbo.consultationData_566_Pat ON dbo.ConsultationData_566.patid = dbo.consultationData_566_Pat.patid
GROUP BY dbo.NonCurrentPat_566.patid, dbo.NonCurrentPat_566.yob + 1800, dbo.consultationData_566_Pat.patid,
dbo.consultationData_566_Pat.MINeventdate, dbo.ConsultationData_566.eventdate
ORDER BY dbo.NonCurrentPat_566.patid

As suggested by Sunitabeck but still I receive the same results, with an additional column of eventdate which has similar values to Mineventdate.

Any help please

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 13:25:42

What I really meant was this - which would make sure that rows don't get dropped out because of the join:


SELECT
   TOP(100) PERCENT dbo.NonCurrentPat_566.patid,
   dbo.NonCurrentPat_566.yob + 1800 AS YrBirth,
   dbo.consultationData_566_Pat.MINeventdate,
   dbo.ConsultationData_566.eventdate
FROM
   dbo.NonCurrentPat_566
   LEFT JOIN dbo.ConsultationData_566
      ON  dbo.NonCurrentPat_566.patid = dbo.ConsultationData_566.patid AND dbo.NonCurrentPat_566.yob 
          + 1800 > YEAR(dbo.ConsultationData_566.eventdate)
   LEFT OUTER JOIN dbo.consultationData_566_Pat
      ON  dbo.ConsultationData_566.patid = dbo.consultationData_566_Pat.patid
GROUP BY
   dbo.NonCurrentPat_566.patid,
   dbo.NonCurrentPat_566.yob + 1800,
   dbo.consultationData_566_Pat.patid,
   dbo.consultationData_566_Pat.MINeventdate,
   dbo.ConsultationData_566.eventdate
ORDER BY
   dbo.NonCurrentPat_566.patid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources