How to Get Distinct Data from Two Same Structured Table using Query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi
I am trying to get data Using Following Query but its not giving me desired result..

VB

Set RSL = Nothing
If RSL.State = 1 Then RSL.Close
If Option2.Value = True Then
RSL.Open "Select BDD.BiltyNo, BDD.Article as Art, BDD.Consignee, BDD.Desti as Destination,  " & _
"BDD.Weight, BDD.Total AS Basic, (CASE WHEN BDD.Pymt_mode = 'TOPAY' THEN BDD.Total ELSE 0 END) as Topay,  " & _
"(CASE WHEN BDD.Pymt_mode = 'PAID' THEN BDD.Total ELSE 0 END) as Paid, " & _
"(CASE WHEN BDD.Pymt_mode = 'TBB' THEN BDD.Total ELSE 0 END) as TBB, BDD.pf as PF_Amt  " & _
"FROM Bilty_Detail_DELV as BDD, B_Detail_DELV_Crosing AS BDC  " & _
"WHERE BDD.BiltyNo <> BDC.BiltyNo AND BDD.Tmp_Weight = " & Text1(0).Text & " group by BDD.BiltyNo, BDD.Article, BDD.Consignee, BDD.Desti, BDD.Weight, BDD.Total, BDD.Pymt_mode, BDD.pf, BDD.Tmp_Weight", Con_Main, adOpenKeyset, adLockOptimistic

Some of Same records are exist in both table.

and i don't want that data which are exist in B_Detail_DELV_Crosing under Tmp_Weight = 1

Please Help..

Posted 13-Feb-13 5:45am

khan2010

Add a Solution

Comments

Richard C Bishop 13-Feb-13 11:48am

You are trying to select from two tables without joining them. Use "Select Distinct" to weed out multiple records that are the same.

khan2010 13-Feb-13 11:52am

Thanks For reply so fast Richcb.
i tried Select Distinct but still its giving me that records which are exist in B_Detail_DELV_Crosing

Richard C Bishop 13-Feb-13 16:59pm

You are selecting from that Table, if you don't want data from it, don't select from it.

2 solutions

Solution 2

I don't know what is RSL, but in the first line you set to RSL=Nothing. Next instructions are "ignored".

VB

Set RSL = Nothing
If RSL.State = 1 Then RSL.Close
If Option2.Value = True Then

Posted 13-Feb-13 6:37am

Maciej Los

Comments

khan2010 13-Feb-13 13:02pm

DIM RSL AS NEW ADODB.RECORDSET

Maciej Los 13-Feb-13 13:10pm

Read this: http://msdn.microsoft.com/en-us/library/office/aa141422%28v=office.10%29.aspx and you'll find an answer what to do next... ;)

khan2010 13-Feb-13 16:12pm

actually Records set is not my problem ,
my problem is how to in my scenario how to get desired data using posted query.

Maciej Los 13-Feb-13 16:23pm

Just test above query in SSMS (SQL Studio Management Studio).

khan2010 13-Feb-13 16:51pm

I tried.. it's not giving me desired result.

Maciej Los 13-Feb-13 17:00pm

Sorry, but only you can solve this problem. Try to use JOINS (LEFT, RIGHT, INNER), rather then cross join. You need to define relationships between tables.
Probably, this part: WHERE BDD.BiltyNo <> BDC.BiltyNo is an element which generate wrong result. Try to change it as fallow: FROM ... AS BDC INNER JOIN ... AS BDD ON BDC.BiltyNo = BDD.BiltyNo.

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)

khan2010 · Accepted Answer · 2013-02-15T21:56:00

I have solved this my self. :-)

Submitting solution for future reference.

Thanks for Helping me. :-)

SQL

RSL.Open "Select Distinct(BDD.BiltyNo), BDD.Article as Art, BDD.Consignee, BDD.Desti as Destination,  " & _
"BDD.Weight, BDD.Total AS Basic, (CASE WHEN BDD.Pymt_mode = 'TOPAY' THEN BDD.Total ELSE 0 END) as Topay,  " & _
"(CASE WHEN BDD.Pymt_mode = 'PAID' THEN BDD.Total ELSE 0 END) as Paid, " & _
"(CASE WHEN BDD.Pymt_mode = 'TBB' THEN BDD.Total ELSE 0 END) as TBB, BDD.pf as PF_Amt  " & _
"FROM Bilty_Detail_DELV as BDD " & _
"WHERE BDD.BiltyNo NOT IN (SELECT DISTINCT(BDC.BiltyNo) FROM B_Detail_DELV_Crosing AS BDC) " & _
" AND BDD.Tmp_Weight = " & Text1(0).Text & _
" group by BDD.BiltyNo, BDD.Article, BDD.Consignee, BDD.Desti, BDD.Weight, BDD.Total,  " & _
" BDD.Pymt_mode, BDD.pf, BDD.Tmp_Weight", Con_Main, adOpenKeyset, adLockOptimistic