September 26, 2008 at 1:55 pm
Good Forum.
I hope you all will find it very well. Sorry for this inconvenience but I need if I could give idea of how to make the following query in a simple and easy, or that method could be applied to the construction and generation of the same in a SELECT in SQL Server 2005:
TABLE: TBL_POINTCONTROL
IdTBL_POINTCONTROL INT IDENTITY (1,1) NOT NULL,
CODE CHAR (5) NOT NULL,
NAME nvarchar (80) NOT NULL,
MILE SMALLINT NOT NULL
Each record that is generated in this table indicates a point control in "x" region of a site "and". The kilometers specifies that the record identifies the point control automotive as such.
Now, the problem is that I need to create a query predefined in the database WHERE I make the difference (subtraction) of the number of miles from the record that there are 2 to register 1, and expressing how much is the distance between those two points, so to speak.
Example data for the table:
IdTBL_PUNTOCONTROL CODE NAME MILES
1 AAA ENCRUCIJADA 10
2 BBB PALO NEGRO 25
3 CCC TAPA TAPA 35
4 DDD SANTA CLARA 60
What I want to me as a result of the query is as follows:
CODE NAME CODE NAME DISTANCE
BBB PALO NEGRO AAA ENCRUCIJADA 15
CCC TAPA TAPA BBB PALO NEGRO 10
DDD SANTA CLARA CCC TAPA TAPA 25
As I think that really is something simple but not give encounter as a solution to this topic. Could someone give me a light solution of the matter.
Thanks,;).
Regards,
Jose Redondo
September 26, 2008 at 4:05 pm
Unless every destination is in the same direction from a common point, your calculation isn't going to return the right result (if AAA is north of "here" and BBB is south, then the distance between the 2 isn't 15. It's also not likely to be 35 either, unless AAA, BBB and "here" are in a straight line.
That being said - it sounds to me that you are looking for an "inequality non-equi join", otherwise known as an exclusive cartesian product. Also know as a triangle join
Something like:
select pt1.*, pt2.*, /*whatever is the correct distance calculation*/ distance
from
(select * from tblpointscontrol) pt1
join
(select * from tblpointscontrol) pt2
on pt1.pointID>pt2.pointID
Will give you all of the non-repeating combinations you need to calculate. Of course - this will grow VERY quickly (essential N*(N-1)) as you add points to your list.
You might care to put "triangular join" into the google box on the top of the page and read up on Jeff's article on the matter. It's a good read since it gives you an idea how fast these joins get out of control.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply