SQL query question

P

Praxis

Hi all,
I have a SQL query question...
I have a table with road segments that have a low and high address
(see sample QUERY below)
I am wanting to produce a report of the roads that meet the
following...

1. [FROM_ADD] > 0
AND
(
2. the [FROM_ADD] > [TO_ADD]
OR
3. if there is more than one segment with the same name...
the [FROM_ADD] < the previous road segment with the same
name's [TO_ADD]
)

So if I ran it on the query below, I would end up with...

AARONDALE ROAD Because third segment's [FROM_ADD]
is less than the previous
segments [TO_ADD]
There are more errors with
AARONDALE ROAD, but only need to list it once
ABETO STREET Because second segment's [FROM_ADD]
is less than the previous
segments [TO_ADD]
FIRST AVENUE Because the [FROM_ADD] > [TO_ADD]


I hope this makes some kind of sense.
I am needing to go back and research the roads with problems, and we
have 4000+ road segments, so it would take a while to look through all
of them manually.

Thanks for any pointers
Bret


Query result sample (Road Segments with address > 0 and ordered by
[FROM_ADD])
[NAME] [FROM_ADD] [TO_ADD]
AARONDALE ROAD 17800 17900
AARONDALE ROAD 17920 18115
AARONDALE ROAD 18015 18245
AARONDALE ROAD 18115 18215
AARONDALE ROAD 18215 18420
ABETO STREET 3850 3780
ABETO STREET 3850 4070
ABETO STREET 4150 4360
ABRAHAM AVENUE 24900 24970
ABRAHAM AVENUE 24970 25110
ABRAHAM AVENUE 25110 25190
ABRAHAM AVENUE 25190 25250
ACORN DRIVE 16120 16240
ACORN DRIVE 16240 16310
ACORN DRIVE 16310 16383
ACORN DRIVE 16383 16416
ACORN DRIVE 16416 16422
ACORN WAY 23970 24020
ADAMS COURT 20582 20660
ADELLEANA LANE 13300 13350
AIRPORT ROAD 10707 10900
ALAMEDA COURT 14590 14601
ALAMEDA DRIVE 2500 2630
ALAMEDA DRIVE 2630 2707
ALAMEDA DRIVE 2707 2730
FIRST AVENUE 2900 2700
 
M

Michel Walsh

From what I understand, if a is the actual record and p is the previous
record, then


WHERE a.from_add > 0 AND (a.from_add > a.to_add OR a.from_add > p.to_add)


is the criteria we need. What is left if to define the actual record and the
'previous' record. If we use:


FROM from_add AS a LEFT JOIN from_add As p ON a.name = p.name AND
a.from_add>p.from_add

it is a nice start to get p defined, but it defines, in fact, all the
records that have the same name, and a lower value for from_add than the
actual one, a.from_add, not JUST the IMMEDIATE previous one. We have to
specify we are only interested in "the one", not all the ones, that preceed,
so:

we can add something in the where clause to that goal:
WHERE p.from_add IS NULL OR p.from_add=(SELECT MAX(c.from_add) FROM from_add
As c
WHERE c.name=a.name AND
a.from_add>c.from_add)

either keep it in the join. and make a HAVING clause:

FROM ( from_add AS a LEFT JOIN from_add As p ON a.name = p.name AND
a.from_add>p.from_add )
LEFT JOIN from_add AS c ON a.name=c.name AND a.from_add>c.from_add
....
GROUP BY a.from_add, p.from_add
HAVING p.from_add=MAX(c.from_add)

(or, a third solution, we could also use a TOP 1 technique, or similar)


And that's about it, well, we have to re-write the initial WHERE clause to
account for p.fields to be null (in case the actual record has NO previous
record):


SELECT whatever
FROM from_add AS a LEFT JOIN from_add As p ON a.name = p.name AND
a.from_add>p.from_add
WHERE (p.from_add IS NULL OR (
p.from_add=(SELECT MAX(c.from_add)
FROM from_add As
c
WHERE
c.name=a.name AND a.from_add>c.from_add)
) )
AND a.from_add > 0
AND (a.from_add > a.to_add OR Nz(a.from_add > p.to_add, -1))

if you use the sub-query.


It does not seem it would be a super fast query.



Vanderghast, Access MVP

Praxis said:
Hi all,
I have a SQL query question...
I have a table with road segments that have a low and high address
(see sample QUERY below)
I am wanting to produce a report of the roads that meet the
following...

1. [FROM_ADD] > 0
AND
(
2. the [FROM_ADD] > [TO_ADD]
OR
3. if there is more than one segment with the same name...
the [FROM_ADD] < the previous road segment with the same
name's [TO_ADD]
)

So if I ran it on the query below, I would end up with...

AARONDALE ROAD Because third segment's [FROM_ADD]
is less than the previous
segments [TO_ADD]
There are more errors with
AARONDALE ROAD, but only need to list it once
ABETO STREET Because second segment's [FROM_ADD]
is less than the previous
segments [TO_ADD]
FIRST AVENUE Because the [FROM_ADD] > [TO_ADD]


I hope this makes some kind of sense.
I am needing to go back and research the roads with problems, and we
have 4000+ road segments, so it would take a while to look through all
of them manually.

Thanks for any pointers
Bret


Query result sample (Road Segments with address > 0 and ordered by
[FROM_ADD])
[NAME] [FROM_ADD] [TO_ADD]
AARONDALE ROAD 17800 17900
AARONDALE ROAD 17920 18115
AARONDALE ROAD 18015 18245
AARONDALE ROAD 18115 18215
AARONDALE ROAD 18215 18420
ABETO STREET 3850 3780
ABETO STREET 3850 4070
ABETO STREET 4150 4360
ABRAHAM AVENUE 24900 24970
ABRAHAM AVENUE 24970 25110
ABRAHAM AVENUE 25110 25190
ABRAHAM AVENUE 25190 25250
ACORN DRIVE 16120 16240
ACORN DRIVE 16240 16310
ACORN DRIVE 16310 16383
ACORN DRIVE 16383 16416
ACORN DRIVE 16416 16422
ACORN WAY 23970 24020
ADAMS COURT 20582 20660
ADELLEANA LANE 13300 13350
AIRPORT ROAD 10707 10900
ALAMEDA COURT 14590 14601
ALAMEDA DRIVE 2500 2630
ALAMEDA DRIVE 2630 2707
ALAMEDA DRIVE 2707 2730
FIRST AVENUE 2900 2700
Click to expand...
 
Top