I want data in unique way in sql.

Question

0.00/5 (No votes)

See more:

Hi,

I have two tables.First table has two rows but different products for perticular invoice.
second table has single entry for that invoice for debit or credit entry.

so i want result both rows from first table and single data row(column) from second table.

InvoiceNum PartNum Credit Debit
154788 CF-000005 0 326
154788 CF-000002

how?

[Added from comment]
First Table
______________________________

InvoiceNum.     PartNum
154788	        CF-000005
154788          CF-000002

_____________________
Second Table

ARInvoiceNum.   BookCreditAmount.  BookDebitAmount
154788.         0.000.             326.000

What I have tried:

select InvoiceNum,PartNum,(select top 1 BookCreditAmount ),(select top 1 BookDebitAmount )
from GLJrnDtl h left join InvcDtl d on h.ARInvoiceNum=d.InvoiceNum
where h.ARInvoiceNum=154788 and SegValue1=46804

Posted 16-Feb-16 19:12pm

Amit Vasi

Updated 16-Feb-16 20:37pm

OriginalGriff

v2

Add a Solution

Comments

Tomas Takac 17-Feb-16 4:11am

It doesn't make much sense to me. Why do you need this?

dharan1990 17-Feb-16 4:17am

Can you please try applying row number on both the tables and apply any of the outer join based on those row number which should resolve.

2 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)

dharan1990 · Answer 1 · 2016-02-16T23:26:00

You can try the below code it should work for your expected output.

select A.inv,a.partnum,isnull(b.Credit,0) as 'Credit',isnull(b.debit,0) as debit 
from(
    SELECT inv,partnum,row_number() over (partition by inv order by inv) as row_num
    FROM #temp1)A
left join
    (SELECT inv,Credit,debit,row_number() over (partition by inv order by inv) as row_num
    FROM #temp2)B
on  A.row_num=B.row_num and a.inv=b.inv
ORDER BY A.inv

Herman<T>.Instance · Answer 2 · 2016-02-16T22:50:00

when subselecting: (select top 1 BookCreditAmount ),(select top 1 BookDebitAmount )
You will always get nothing. There is NO FROM clause.

So your query should look something like:

SQL

select h.InvoiceNum,h.PartNum, d.BookCreditAmount, d.BookDebitAmount 
from GLJrnDtl as h 
left join InvcDtl d on 
h.ARInvoiceNum=d.InvoiceNum
where h.ARInvoiceNum=154788 
and h.SegValue1=46804

That leaves you with the point that any line > 1 will show the amounts too.
So you need to check on Row_NUMBER() thus query might be:

SQL

select InvoiceNum, PartNum, BookCreditAmount, BookDebitAmount
FROM 
(
	select ROW_NUMBER() OVER (ORDER BY h.ARInvoiceNum) AS ROWNR,
		h.InvoiceNum,h.PartNum, d.BookCreditAmount, d.BookDebitAmount 
	from GLJrnDtl as h 
	left join InvcDtl d on 	h.ARInvoiceNum=d.InvoiceNum
	
) as row1
WHERE rownr=1
and row1.ARInvoiceNum=154788 
	and row1.SegValue1=46804
UNION
select InvoiceNum, PartNum, NULL, NULL
FROM 
(
	select ROW_NUMBER() OVER (ORDER BY h.ARInvoiceNum) AS ROWNR,
		h.InvoiceNum,h.PartNum, d.BookCreditAmount, d.BookDebitAmount 
	from GLJrnDtl as h 
	left join InvcDtl d on 	h.ARInvoiceNum=d.InvoiceNum
) as rowOther
where rownr > 1 and rowOther.ARInvoiceNum=154788 
and rowOther.SegValue1=46804

I want data in unique way in sql.

2 solutions

Solution 4

Solution 3

Add your solution here

Preview 0