query to select distinct records from two tables from same data base

Question

1.00/5 (2 votes)

See more:

TABLE_A

AUTO_ID IMEI LATITUDE LONGITUDE TIME_STAMP

1        111  19.77    18.88      2:00:00
2        111  19.77    18.88      2:20:00
3        111  19.77    18.88      2:30:00

TABLE_B

AUTO_ID IMEI LATITUDE LONGITUDE TIME_STAMP

1        111  19.77    18.88      2:00:00
2        111  19.77    18.88      2:20:00
3        111  19.77    18.88      2:30:00
4        123  20.20    21.24      4:00:00

So I need query to select only this row( 4 123 20.20 21.24 4:00:00)
from the above two tables

Posted 1-Mar-12 20:07pm

kalyan10qwerty

Updated 1-Mar-12 20:10pm

walterhevedeich

v3

Add a Solution

Comments

walterhevedeich 2-Mar-12 2:12am

I don't see a reason why you need TABLE_A to get that row from TABLE_B. Won't SELECT * FROM TABLE_B WHERE AUTO_ID = 4 work?

Sergey Alexandrovich Kryukov 17-Jun-13 2:50am

Please stop posting non-answers as "solution". It can give you abuse reports which eventually may lead to cancellation of your CodeProject membership. And the fact you even self-accepted some formally is just outrageous, a sure way for a ban. I hope you won't do it after this warning.

Comment on any posts, reply to available comments, or use "Improve question" (above).
Also, keep in mind that members only get notifications on the post sent in reply to there posts.
—SA

5 solutions

Solution 1

if you want to select only this row( 4 123 20.20 21.24 4:00:00)

SQL

select * from table_B where IMEI not in(Select IMEI from table_A)

Posted 1-Mar-12 20:15pm

uspatel

Comments

kalyan10qwerty 2-Mar-12 3:09am

i just gave you the sample table.i need to filter data from two table.for the above sample your query works fine:-) suppose if it gives same lat and long for the same imei then again issue raises

Solution 2

hi there...

first u tell from which table u wants records
or
do join query and take condition in WHERE clause like col1table1<> col1table2 OR col2table2 <> col2table2 .........

try this query,i m assuming u hve one more table TABLE_C WITH SAME STRUCTURE AS table_b,

SQL

INSERT INTO TABLE_C
SELECT distinct b.* FROM TABLE_B b ,TABLE_A a
WHERE b.IMEI NOT IN(SELECT a.IMEI FROM TABLE_A a)
OR
(b.IMEI=a.IMEI AND (b.LATITUDE<>a.LATITUDE OR b.LONGITUDE<>a.LONGITUDE))

hope this helps..
revert back with ur comments...

Posted 1-Mar-12 20:24pm

rajdeep kumar

Updated 1-Mar-12 23:10pm

v3

Comments

kalyan10qwerty 2-Mar-12 3:12am

TABLE_A
AUTO_ID IMEI LATITUDE LONGITUDE TIME_STAMP

1 111 19.77 18.88 2:00:00
2 111 19.77 18.88 2:20:00
3 111 19.77 18.88 2:30:00

TABLE_B
AUTO_ID IMEI LATITUDE LONGITUDE TIME_STAMP

1 111 19.77 18.88 2:00:00
2 111 19.77 18.88 2:20:00
3 111 19.77 18.88 2:30:00
4 123 24.20 21.24 4:10:00
5 124 25.20 22.24 4:20:00
6 125 26.20 23.24 4:30:00

I NEED TO SELECT DISTINCT RECORDS FROM THE ABOVE TO TABLES AND THE RESULTANT WILL BE STORED IN A NEW TABLE. NEED TOTAL ROW NOT A COLUMN

Solution 6

Hi,

Distinct keyword is used to displays different records but make sure you are using order by or group by clause for the on the field that must be in the select query.

Posted 2-Mar-12 0:39am

Aditya Bhushan Chaturvedi

Solution 3

we have a data base called raw data in which all the above formatted date will fall in to raw data data base.This data will come from a gprs device.so each unit consist of one gprs device.so all messages are date(what ever it may be) will b collected by one data base which contain each table for each device.so if a messages are date(what ever it may be) which consist of same latitude longitude timestamp etc etc will fall in to different tables so i need to filter the data and fill in to a new data base which consist of single table

Posted 1-Mar-12 20:43pm

kalyan10qwerty

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)

kalyan10qwerty · Accepted Answer · 2012-03-01T21:02:00

TABLE_A
AUTO_ID IMEI LATITUDE LONGITUDE TIME_STAMP

1 111 19.77 18.88 2:00:00
2 111 19.77 18.88 2:20:00
3 111 19.77 18.88 2:30:00

TABLE_B
AUTO_ID IMEI LATITUDE LONGITUDE TIME_STAMP

1 111 19.77 18.88 2:00:00
2 111 19.77 18.88 2:20:00
3 111 19.77 18.88 2:30:00
4 123 24.20 21.24 4:10:00
5 124 25.20 22.24 4:20:00
6 125 26.20 23.24 4:30:00

I NEED TO SELECT DISTINCT RECORDS FROM THE ABOVE TO TABLES AND THE RESULTANT WILL BE STORED IN A NEW TABLE. NEED TOTAL ROW NOT A COLUMN

query to select distinct records from two tables from same data base

5 solutions

Solution 1

Solution 2

Solution 6

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us