Query returns a few extra records-CodePudding

I have these tables and this query in an Access database:

samples

hole_id    | depth_from | depth_to  |
DH001        100          105
DH001        105          120
DH001        110          115
DH001        115          120

overlapping_samples (and therefore the correct output)

hole_id    | depth_from | depth_to  |
DH001        110          115
DH001        115          120

query

SELECT a.*
FROM samples AS a
INNER JOIN overlapping_samples AS o
ON a.hole_id=o.hole_id
WHERE a.hole_id=o.hole_id AND a.depth_to=o.depth_to
;

results

hole_id   | depth_from | depth_to  |
DH001       100          105
DH001       110          115
DH001       115          120

It's very simple. The result is almost ok, but it includes some extra records from the left table (i.e. samples). In fact, in the example above it may not necessarily return the extra row. Only a small percentage are.

If not obvious, I want to return all the records from the left table that match to the right table. The right table is actually a subset of the left, and therefore the query should have the same number of records. It's intended for a DELETE statement, but

CodePudding user response：

i've changed your query to:

SELECT a.hole_id as ahole_id, a.depth_from as adepth_from, a.depth_to as adepth_to,o.hole_id as ohole_id, o.depth_from as odepth_from, o.depth_to as odepth_to
FROM samples AS a
LEFT JOIN overlapping_samples AS o ON a.hole_id=o.hole_id AND a.depth_to=o.depth_to AND a.depth_from=o.depth_from
WHERE a.hole_id=o.hole_id AND a.depth_to=o.depth_to;

and it gave me this result

ahole_id    | adepth_from   | adepth_to | ohole_id  | odepth_from   | odepth_to |
DH001       110             115          DH001       110                115
DH001       115             120          DH001       115                120

is that what you were looking for?

CodePudding user response：

this may work:

SELECT a.*
FROM samples a
JOIN overlapping_samples o ON a.hole_id = o.hole_id
WHERE a.depth_from = o.depth_from 
  AND a.depth_to = o.depth_to;

I fixed a problem in WHERE clause, from:

a.hole_id=o.hole_id

to:

a.depth_from = o.depth_from

hole_id is already present in JOIN ... ON a.hole_id = o.hole_id

if you still don't get correct count you may need to look at your data and add some extra condition either in WHERE or JOIN clause