Automatically duplicate rows in SQL server 2008 r2 select query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

automatically duplicate rows in sql server 2008 r2 select query
i have two table name 1) tbl_Stock_Journal 2) tbl_Stock_Journal_Details

i run a select query with where condition
i have 5 data in table1 and in table 2 have 3 data when i run the query result is showing 15 row the query show on date wise
i want to maximum data show and if other table in same date have no number of data then show blank or null

please help me.....

What I have tried:

ALter proc P_GetStockJournalRPT --  'all', 'all','2018-07-10' , '2018-07-10'     

   
@SJItmID varchar (100)      ,          
@SJDItmID varchar (100)      , 
@Sdate date,                                  
@EDate Date  
   
As
if @SJItmID='ALL' and @SJDItmID='ALL'
begin
select distinct SJ.SJID, i1.itemnm as itemnmsou, SJ.SJqty, SJ.SJPCS, SJ.SJRate, SJ.SJamt, SJ.SJleft, SJ.SJDate, SJ.nrr,
i2.itemnm as itemnmDES, SJD.SJDqty, SJD.SJDPCS, SJD.SJDRate, SJD.SJDamt, SJD.SJDstock, SJD.SJDdate

from tbl_Stock_Journal SJ

INNER JOIN tbl_item AS i1   ON i1.itemid  =SJ.SJItmID
 inner JOIN tbl_Stock_Journal_Details AS SJD   ON   SJ.SJID = SJD.SJID                             
inner JOIN tbl_item AS i2   ON i2.itemid  =SJd.SJdItmID

where CAST(sj.SJDate AS DATE) BETWEEN @Sdate AND @EDate         
--where CAST(sj.SJDate AS DATE) BETWEEN '2018-07-10' AND '2018-07-10'        
end

Posted 23-Jul-18 19:20pm

Jayanta Modak

Updated 24-Jul-18 0:03am

Add a Solution

Comments

Wendelius 24-Jul-18 5:02am

Could post some example data and an example of the desired output?

Jayanta Modak 27-Jul-18 23:01pm

I was very sick. So, I could not answer. Please forgive me ...
https://ibb.co/jfoJM8
https://ibb.co/fgOk18

Naga Sindhura 25-Jul-18 9:27am

try to change the from part as follows:

from tbl_Stock_Journal SJ
INNER JOIN tbl_item AS i1 ON i1.itemid =SJ.SJItmID
inner JOIN tbl_Stock_Journal_Details AS SJD ON SJ.SJID = SJD.SJID AND SJd.SJdItmID = i1.itemid

If it does work, try to post some sample data.

Jayanta Modak 27-Jul-18 23:01pm

I was very sick. So, I could not answer. Please forgive me ...
https://ibb.co/jfoJM8
https://ibb.co/fgOk18

Jayanta Modak 28-Jul-18 1:21am

Sir it is showing no error, but it showing only same match of items of two tables

1 solution

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)

CHill60 · Answer 1 · 2018-07-24T00:04:00

I think what you are describing would be the results if you used a LEFT OUTER JOIN instead of the INNER JOINs that you currently have.

Have a look at these CodeProject articles to explain further
Visual Representation of SQL Joins[^]
Types of Join in SQL Server[^]

Not a lot of change to make...

SQL

select distinct SJ.SJID, i1.itemnm as itemnmsou, SJ.SJqty, SJ.SJPCS, SJ.SJRate, SJ.SJamt, SJ.SJleft, SJ.SJDate, SJ.nrr,
i2.itemnm as itemnmDES, SJD.SJDqty, SJD.SJDPCS, SJD.SJDRate, SJD.SJDamt, SJD.SJDstock, SJD.SJDdate

from tbl_Stock_Journal SJ

LEFT OUTER JOIN tbl_item AS i1   ON i1.itemid  =SJ.SJItmID
LEFT OUTER JOIN tbl_Stock_Journal_Details AS SJD   ON   SJ.SJID = SJD.SJID                             
LEFT OUTER JOIN tbl_item AS i2   ON i2.itemid  =SJd.SJdItmID

where CAST(sj.SJDate AS DATE) BETWEEN @Sdate AND @EDate