how to select data from select statment..

Question

1.00/5 (1 vote)

See more:

SQL

select dw.C_DSC_Code,MAX(dr.d_date_report) from TBL_DWR_DETAILS dw
left join tbl_dwr dr on dr.N_Srno=dw.N_Srno
 where  dw.C_DSC_Code  in ( 'D34848',
'D34796','D34799','D34801',
'D34712','D34772','D34784','D34785',
'D34787','D34816','D34765','D34779',
'D34770','D34755','D34782','D34822',
'D34687','D34817','D34759','D34777',
'D34750','D34778','D044633','D34746',
'D34749','D34819','D34747','D34769',
'D34781','D34748','D34824','D34834',
'D34776','D34670','D34763','D34757',
'D34827','D34756','D34760','D34690',
'D34739','D34716','D34768','D34825',
'D34766','D34741','D34713')
 group by
dw.C_DSC_Code

In this qvery i want to see all 47 doctors inside the bracket..but it fetches only matching values from tbl_dwr_details table i.e only 44 records but i need unmatching record of 3doctors..how to make thar pls help me out

Posted 16-Mar-15 20:31pm

Member 11337367

Updated 16-Mar-15 20:36pm

Praneet Nadkar

v2

Add a Solution

Comments

Maciej Los 17-Mar-15 2:46am

Please, be more specific and provide more details, such as input data and expected output. Do not forget to provide information about structure ot tables.

Jörgen Andersson 17-Mar-15 7:56am

I think you may have swapped the tables.

_Maxxx_ 17-Mar-15 16:39pm

Maciej and Jorgen are right - make the dw table the joined-to table, and check for nulls as I suggested - so

select dw.C_DSC_Code,MAX(dr.d_date_report)
from tbl_dwr dr
left join TBL_DWR_DETAILS dw on dr.N_Srno=dw.N_Srno
where dw.C_DSC_Code in ( ...)
or dw.C_DSC_Code is null

Member 11337367 18-Mar-15 0:46am

same result no changes

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Maxxx_ · Answer 1 · 2015-03-16T20:54:00

Solution 2

just add

or dw.C_DSC_Code is null

after the closing bracket of the list of codes

Posted 16-Mar-15 20:54pm

_Maxxx_

Comments

Maciej Los 17-Mar-15 3:05am

Maxxx, dw is a table on right side. So, it means: get all data from dr table and only that data from dw tablt that matches to dr. In my opnion answer is wrong (neutral comment).

Saral S Stalin · Answer 2 · 2015-03-16T21:24:00

Solution 3

Try like below and let me know if this works.

SQL

select dw.C_DSC_Code,MAX(dr.d_date_report)
from tbl_dwr dr
left join TBL_DWR_DETAILS dw on dr.N_Srno=dw.N_Srno
 AND  dw.C_DSC_Code  in ( 'D34848',
'D34796','D34799','D34801',
'D34712','D34772','D34784','D34785',
'D34787','D34816','D34765','D34779',
'D34770','D34755','D34782','D34822',
'D34687','D34817','D34759','D34777',
'D34750','D34778','D044633','D34746',
'D34749','D34819','D34747','D34769',
'D34781','D34748','D34824','D34834',
'D34776','D34670','D34763','D34757',
'D34827','D34756','D34760','D34690',
'D34739','D34716','D34768','D34825',
'D34766','D34741','D34713')
 group by
dw.C_DSC_Code

Posted 16-Mar-15 21:24pm

Saral S Stalin

Comments

Member 11337367 17-Mar-15 3:30am

No changes except only one column coming more with doctor code column as null...

Saral S Stalin 17-Mar-15 4:24am

That is because you have a group by clause. Can you post the expected output?

Member 11337367 17-Mar-15 4:49am

C_DSC_Code date
NULL 2015-03-17 00:00:00.000
D044633 2014-05-16 00:00:00.000
D34670 2013-10-29 00:00:00.000
D34687 2014-05-22 00:00:00.000
D34690 2014-11-26 00:00:00.000
D34712 2014-12-05 00:00:00.000
D34713 2015-01-20 00:00:00.000
D34716 2013-07-30 00:00:00.000
D34739 2014-06-10 00:00:00.000
D34741 2014-12-06 00:00:00.000
D34746 2014-12-01 00:00:00.000
D34747 2014-06-11 00:00:00.000
D34748 2014-04-04 00:00:00.000
D34749 2014-06-10 00:00:00.000
D34750 2014-06-19 00:00:00.000
D34755 2015-01-23 00:00:00.000
D34756 2014-02-14 00:00:00.000
D34757 2013-10-31 00:00:00.000
D34759 2014-04-23 00:00:00.000
D34763 2013-11-15 00:00:00.000
D34765 2014-06-03 00:00:00.000
D34766 2014-01-24 00:00:00.000
D34768 2013-10-24 00:00:00.000
D34769 2013-11-19 00:00:00.000
D34770 2014-05-08 00:00:00.000
D34772 2014-04-28 00:00:00.000
D34778 2013-11-20 00:00:00.000
D34779 2014-06-10 00:00:00.000
D34781 2013-11-26 00:00:00.000
D34782 2014-02-15 00:00:00.000
D34784 2014-02-17 00:00:00.000
D34785 2014-04-28 00:00:00.000
D34787 2015-01-08 00:00:00.000
D34796 2014-12-12 00:00:00.000
D34799 2014-12-12 00:00:00.000
D34801 2014-12-12 00:00:00.000
D34816 2014-08-20 00:00:00.000
D34817 2014-12-01 00:00:00.000
D34819 2013-11-12 00:00:00.000
D34822 2013-11-19 00:00:00.000
D34824 2015-01-07 00:00:00.000
D34825 2013-07-26 00:00:00.000
D34827 2015-01-23 00:00:00.000
D34834 2015-01-28 00:00:00.000
D34848 2015-02-16 00:00:00.000

This is the output we got..

Saral S Stalin 17-Mar-15 5:13am

What is the output you are expecting?

Pankil Bhatt · Answer 3 · 2015-03-16T20:47:00

Solution 1

Hello,

I think you have to use the right join.

Thanks
Pankil

Posted 16-Mar-15 20:47pm

Pankil Bhatt

v2

Comments

Maciej Los 17-Mar-15 2:53am

Sounds like comment, especially when you did not provide more details about how to use it.