Display Non-duplicate Records

[thegentleman]

Hi,

I have two identical tables A and B both with similar sets of data. What I want to do is create a query that displays all of the data in table A where one field (X) is the same, but where field (Y) is not the same in table B.

E.G

TABLE A

ID BOOKING VALUE
23 6876 23
23 6876 768
23 6876 545
25 7687 9879
25 7687 765
25 7687 3345

TABLE B

ID BOOKING VALUE
23 6876 23
23 6876 768
24 7888 67
25 7687 9879
25 7687 765

The resulting Query should look like:

ID BOOKING VALUE
23 6876 545
25 7687 3345

The query must also ignore all lines in table B that are not in table A.

I hope this makes sense. Any help would be appreciated.

~tg

 

[PHV]

Something like this ?[tt]
SELECT A.ID, A.BOOKING, A.VALUE
FROM TableA A
WHERE NOT EXISTS(
SELECT * FROM TableB B
WHERE B.ID=A.ID And B.BOOKING=A.BOOKING And B.VALUE=A.VALUE
);[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[thegentleman]

Thanks PHV, that's perfect!

I wonder of ou could help me with another problem I am having with this same set of data?

Again I'm comparing the two and I want to produce a set of results where the Booking is the same and where the value field matches within a given tolerance (say 10%). I have the query written but unfortunately the resulting table returns the same line from table A every time it finds a match. I only want one record to be return per matching line.

For example:

Table A

BOOKING VALUE
6876 23
6876 768
6876 545
6876 545
7687 9879
7687 765
7687 100

Table B

BOOKING VALUE
6876 25
6876 800
6876 700
6876 570
7687 10000
7687 10800
7687 96

The resulting query should look like:

BOOKING VALUE_A VALUE_B
6876 23 25
6876 768 800
6876 545 570
7687 9879 10000
7687 96 100

Because all of the A values displayed are within 10% of the values in table B.

The query I'm currently using would bring back the following result using the above data:

BOOKING VALUE_A VALUE_B
6876 23 25
6876 768 800
6876 768 700
6876 545 570
7687 9879 10000
7687 96 100

I don't want to see the line:

6876 768 700

Because the value 768 has already been matched against 800. How do I alter my SQL statement to prevent duplicate lines from occuring?

~tg

 

[PHV]

How do I alter my SQL statement to prevent duplicate lines from occuring?
Which SQL statement ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[thegentleman]

Well, at the moment it goes something like:

SELECT TABLEA.BOOKING, TABLEA.VALUE, TABLEB.VALUE FROM TABLEA INNER JOIN TABLEB ON TABLEA.VALUE = TABLEB.VALUE
WHERE TABLEB.VALUE >=(TABLEA.VALUE-(([TABLEA.VALUE]/100)*10)) And TABLEB.VALUE <=(TABLEA.VALUE+(([TABLEA.VALUE]/100)*10))

 

[PHV]

Try something like this (typed not tested):
SELECT A.BOOKING, A.VALUE AS Value_A, S.VALUE AS Value_B
FROM TABLEA A INNER JOIN (SELECT TOP 1 B.BOOKING,B.VALUE FROM TABLEB B WHERE B.BOOKING=A.BOOKING AND B.VALUE Between 0.9*A.VALUE And 1.1*A.VALUE] S ON A.BOOKING=S.BOOKING;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[thegentleman]

Thanks for the input PH but unfortunately I cannot get this to work. Is it possible to do it in two stages? The first query making a table and then the second removing the records I don't want?

~tg

 

[PHV]

Ok, yet another try (typed AND tested)
SELECT A.BOOKING,A.VALUE AS Value_A,Min(B.VALUE) AS Value_B
FROM TABLEA A INNER JOIN TABLEB B ON A.BOOKING=B.BOOKING
WHERE B.VALUE Between 0.9*A.VALUE AND 1.1*A.VALUE
GROUP BY A.BOOKING,A.VALUE

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244