want to create table from old table

old table has place names, say a to z.

new table has to list all posssible connections, ie, a-b ,a-c , b -a .

can anyone point me in the right direction for how to do this?

tia.

Re: want to create table from old table

can you be more specific?

What are your columns what do you want to copy and to where..



"strawberry" <1@2.com> wrote in message
news:vjZKf.11778$bw1.10372@newsfe2-win.ntli.net...
> old table has place names, say a to z.
>
> new table has to list all posssible connections, ie, a-b ,a-c , b -a .
>
> can anyone point me in the right direction for how to do this?
>
> tia.
>
>

Re: want to create table from old table

the columns are

location, longitude, latitude,

i want to makeinto
location a, location b.

where each location is mapped to each other ie, point a has conenctions to
b -z, b has connections to a, and c-z.

"Simon" wrote in message
news:M91Lf.69546$0N1.44349@newsfe5-win.ntli.net...
> can you be more specific?
>
> What are your columns what do you want to copy and to where..
>
>
>
> "strawberry" <1@2.com> wrote in message
> news:vjZKf.11778$bw1.10372@newsfe2-win.ntli.net...
>> old table has place names, say a to z.
>>
>> new table has to list all posssible connections, ie, a-b ,a-c , b -a .
>>
>> can anyone point me in the right direction for how to do this?
>>
>> tia.
>>
>>
>
>

Re: want to create table from old table

Can you give an example



"strawberry" <1@2.com> wrote in message
news:QH1Lf.13991$gB4.10999@newsfe4-gui.ntli.net...
> the columns are
>
> location, longitude, latitude,
>
> i want to makeinto
> location a, location b.
>
> where each location is mapped to each other ie, point a has conenctions to
> b -z, b has connections to a, and c-z.
>
> "Simon" wrote in message
> news:M91Lf.69546$0N1.44349@newsfe5-win.ntli.net...
> > can you be more specific?
> >
> > What are your columns what do you want to copy and to where..
> >
> >
> >
> > "strawberry" <1@2.com> wrote in message
> > news:vjZKf.11778$bw1.10372@newsfe2-win.ntli.net...
> >> old table has place names, say a to z.
> >>
> >> new table has to list all posssible connections, ie, a-b ,a-c , b -a .
> >>
> >> can anyone point me in the right direction for how to do this?
> >>
> >> tia.
> >>
> >>
> >
> >
>
>

Re: want to create table from old table

strawberry wrote:
> old table has place names, say a to z.
>
> new table has to list all posssible connections, ie, a-b ,a-c , b -a .
>
> can anyone point me in the right direction for how to do this?
>
> tia.
>
>
insert into links
(select from.name, from.lat, from.lon, to.name, to.lat, to.lon
from orig_table from
join orig_table to on from.name<>to.name)

should be close to what you want.

Re: want to create table from old table

"Bob Stearns" wrote in message
news:%H3Lf.978$BS6.341@fe04.lga...
> strawberry wrote:
>> old table has place names, say a to z.
>>
>> new table has to list all posssible connections, ie, a-b ,a-c , b -a .
>>
>> can anyone point me in the right direction for how to do this?
>>
>> tia.
> insert into links
> (select from.name, from.lat, from.lon, to.name, to.lat, to.lon
> from orig_table from
> join orig_table to on from.name<>to.name)
>
> should be close to what you want.

so that links all names that aren't the same?

Re: want to create table from old table

strawberry wrote:
> "Bob Stearns" wrote in message
> news:%H3Lf.978$BS6.341@fe04.lga...
>
>>strawberry wrote:
>>
>>>old table has place names, say a to z.
>>>
>>>new table has to list all posssible connections, ie, a-b ,a-c , b -a .
>>>
>>>can anyone point me in the right direction for how to do this?
>>>
>>>tia.
>>
>>insert into links
>> (select from.name, from.lat, from.lon, to.name, to.lat, to.lon
>> from orig_table from
>> join orig_table to on from.name<>to.name)
>>
>>should be close to what you want.
>
>
> so that links all names that aren't the same?
>
>
Yes. That was what I red the specs as meaning.

Re: want to create table from old table

strawberry a écrit :
> the columns are
>
> location, longitude, latitude,
>
> i want to makeinto
> location a, location b.
>
> where each location is mapped to each other ie, point a has conenctions to
> b -z, b has connections to a, and c-z.
>

Bonjour,

select
tb1.location,tb2.location,tb1.longitude,tb1.latitude,tb2.lon gitude,tb2.latitude
from table_location tb1,table_location tb2
where tb1.location <> tb2.location ;

<> => Not equal

GR

> "Simon" wrote in message
> news:M91Lf.69546$0N1.44349@newsfe5-win.ntli.net...
>
>>can you be more specific?
>>
>>What are your columns what do you want to copy and to where..
>>
>>
>>
>>"strawberry" <1@2.com> wrote in message
>>news:vjZKf.11778$bw1.10372@newsfe2-win.ntli.net...
>>
>>>old table has place names, say a to z.
>>>
>>>new table has to list all posssible connections, ie, a-b ,a-c , b -a .
>>>
>>>can anyone point me in the right direction for how to do this?
>>>
>>>tia.
>>>
>>>
>>
>>
>
>

Re: want to create table from old table

"Bob Stearns" wrote in message
news:Tz8Lf.123$3%4.95@fe02.lga...
> strawberry wrote:
>> "Bob Stearns" wrote in message
>> news:%H3Lf.978$BS6.341@fe04.lga...
>>
>>>strawberry wrote:
>>>
>>>>old table has place names, say a to z.
>>>>
>>>>new table has to list all posssible connections, ie, a-b ,a-c , b -a .
>>>>
>>>>can anyone point me in the right direction for how to do this?
>>>>
>>>>tia.
>>>
>>>insert into links
>>> (select from.name, from.lat, from.lon, to.name, to.lat, to.lon
>>> from orig_table from
>>> join orig_table to on from.name<>to.name)
>>>
>>>should be close to what you want.
>>
>>
>> so that links all names that aren't the same?
> Yes. That was what I red the specs as meaning.

exactly what i wanted! , many thanks, shall implement this afternoon.

Re: want to create table from old table

"Bob Stearns" wrote in message
news:Tz8Lf.123$3%4.95@fe02.lga...
> strawberry wrote:
>> "Bob Stearns" wrote in message
>> news:%H3Lf.978$BS6.341@fe04.lga...
>>
>>>strawberry wrote:
>>>
>>>>old table has place names, say a to z.
>>>>
>>>>new table has to list all posssible connections, ie, a-b ,a-c , b -a .
>>>>
>>>>can anyone point me in the right direction for how to do this?
>>>>
>>>>tia.
>>>
>>>insert into links
>>> (select from.name, from.lat, from.lon, to.name, to.lat, to.lon
>>> from orig_table from
>>> join orig_table to on from.name<>to.name)
>>>
>>>should be close to what you want.
>>
>>
>> so that links all names that aren't the same?
> Yes. That was what I red the specs as meaning.
not much joy with that i'm afriad. original table is called postcode_names ,
and field is called town, icreated a table called links , with id, fromtown
and to town as fields, so i used

insert into links
(select from.Town, to.Town
from postcode_data from
join postcode_data to on from.Town<>to.Town)

i think i'm mssing quite a big trick here!

Re: want to create table from old table

"strawberry" <1@2.com> wrote in message
news:QH1Lf.13991$gB4.10999@newsfe4-gui.ntli.net...
> the columns are
>
> location, longitude, latitude,
>
> i want to makeinto
> location a, location b.
>
> where each location is mapped to each other ie, point a has conenctions to
> b -z, b has connections to a, and c-z.


alter your table and create an id column loc_id as auto_increment, PRIMARY
KEY.
create another table as follows:

CREATE TABLE connections (
conn_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
conn_from INTEGER UNSIGNED NOT NULL DEFAULT '0',
conn_to INTEGER UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY(conn_id),
FOREIGN KEY (conn_from) REFERENCES locations(loc_id) ON DELETE CASCADE
ON UPDATE NO ACTION,
FOREIGN KEY (conn_to) REFERENCES locations(loc_id) ON DELETE CASCADE ON
UPDATE NO ACTION,
) ENGINE=InnoDB;

with the FOREIGN KEY constraints (optional) you must insert the locations
first before inserting the connections.

point a can have as many connections as it needs. just insert rows with
conn_from=loc_id of location a.

of course, if you don't plan to have that many locations in the future and
you can get by with a single char, then just change the types above from
integer to CHAR(1).

>
> "Simon" wrote in message
> news:M91Lf.69546$0N1.44349@newsfe5-win.ntli.net...
>> can you be more specific?
>>
>> What are your columns what do you want to copy and to where..
>>
>>
>>
>> "strawberry" <1@2.com> wrote in message
>> news:vjZKf.11778$bw1.10372@newsfe2-win.ntli.net...
>>> old table has place names, say a to z.
>>>
>>> new table has to list all posssible connections, ie, a-b ,a-c , b -a .
>>>
>>> can anyone point me in the right direction for how to do this?
>>>
>>> tia.
>>>
>>>
>>
>>
>
>