How to join two tables without repeating data from both the tables?

Question

0.00/5 (No votes)

See more:

I want to join 2 tables,I wrote following query.But I'm getting duplicate records

SQL

SELECT  T.TCode,V.VehicleID
   FROM TblTransactions T 						
   JOIN TblConfirmedVehicleMap V ON V.ReplyID=T.ReplyID
  WHERE T.LoadID=1

I got output like:
TCode VehicleID
-------------------------- -----------
TN160200000011 1
TN160200000011 3
TN160200000011 4
TN160200000012 1
TN160200000012 3
TN160200000012 4
TN160200000013 1
TN160200000013 3
TN160200000013 4

I want output like:
TCode VehicleID
-------------------------- -----------
TN160200000011 1
TN160200000012 3
TN160200000013 4

TN160200000011,TN160200000012 & TN160200000011 are TCode,whereas 1,2 & 3 are VehicleID

What I have tried:

I have tried so many things.Joins,union all,intersect,but not able to get desire output.

Posted 10-Feb-16 20:24pm

Ankita1391

Updated 10-Feb-16 20:46pm

_Asif_

v2

Add a Solution

Comments

_Asif_ 11-Feb-16 2:44am

seems like you are missing a join condition. Can you share the structure of TblTransactions and TblConfirmedVehicleMap

Ankita1391 11-Feb-16 2:54am

TblTransactions has following columns:
TransactionID,LoadID,TCode,ReplyID,Weight,Rate,RateType,Amount,CreatedDate

TblConfirmedVehicleMap has following columns:
MapID,VehicleID,ReplyID,CreatedDate

Both tables have ReplyID in common.

_Asif_ 11-Feb-16 5:21am

Can you share the results of below two queries
select TransactionID, ReplyID from TblTransactions where TransactionID in ('TN160200000011', 'TN160200000012', 'TN160200000013');
select VehicleID,ReplyID from TblConfirmedVehicleMap where VehicleID in (1, 2, 3);

Manoj Sawant 11-Feb-16 5:10am

which is the common column between the 2 table

Maciej Los 11-Feb-16 7:31am

I think you have to provide sample data stored in both tables and expected output. Please, use Improve question widget to update your question.

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)

Brittle1618 · Answer 1 · 2016-02-11T00:45:00

Try this

SQL

SELECT  T.TCode, (Select distinct V.VehicleID from TblConfirmedVehicleMap V where  V.ReplyID=T.ReplyID)
FROM TblTransactions T 						
WHERE T.LoadID=1

But I fear that there are repeating Reply IDs in either or both the tables. If so and you still want one of the Reply IDs to get selected then try this:

SQL

SELECT  T.TCode, (Select V.VehicleID from TblConfirmedVehicleMap V where  V.ReplyID=T.ReplyID and rownum = 1)
FROM TblTransactions T
WHERE T.LoadID=1

Hope it helps :)

johnwalker10 · Answer 2 · 2016-02-11T17:28:00

Solution 3

This link will help you to resolve you query: http://www.tutorialspoint.com/sql/sql-using-joins.htm

Posted 11-Feb-16 17:28pm

johnwalker10