Home > Archive > PHP SQL > February 2006 > want to create table from old table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
want to create table from old table
|
|
| strawberry 2006-02-22, 7:57 am |
| 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.
| |
|
| 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.
>
>
| |
| strawberry 2006-02-22, 6:57 pm |
| 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" <simon@webworx.co.uk> 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...
>
>
| |
|
| 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" <simon@webworx.co.uk> wrote in message
> news:M91Lf.69546$0N1.44349@newsfe5-win.ntli.net...
>
>
| |
| Bob Stearns 2006-02-22, 6:57 pm |
| 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.
| |
| strawberry 2006-02-22, 6:57 pm |
|
"Bob Stearns" <rstearns1241@charter.net> wrote in message
news:%H3Lf.978$BS6.341@fe04.lga...
> strawberry wrote:
> 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?
| |
| Bob Stearns 2006-02-22, 9:56 pm |
| strawberry wrote:
> "Bob Stearns" <rstearns1241@charter.net> wrote in message
> news:%H3Lf.978$BS6.341@fe04.lga...
>
>
>
> so that links all names that aren't the same?
>
>
Yes. That was what I red the specs as meaning.
| |
|
| 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.longitude,tb2.latitude
from table_location tb1,table_location tb2
where tb1.location <> tb2.location ;
<> => Not equal
GR
> "Simon" <simon@webworx.co.uk> wrote in message
> news:M91Lf.69546$0N1.44349@newsfe5-win.ntli.net...
>
>
>
| |
| strawberry 2006-02-23, 7:56 am |
|
"Bob Stearns" <rstearns1241@charter.net> wrote in message
news:Tz8Lf.123$3%4.95@fe02.lga...
> strawberry wrote:
> Yes. That was what I red the specs as meaning.
exactly what i wanted! , many thanks, shall implement this afternoon.
| |
| strawberry 2006-02-23, 6:59 pm |
|
"Bob Stearns" <rstearns1241@charter.net> wrote in message
news:Tz8Lf.123$3%4.95@fe02.lga...
> strawberry wrote:
> 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!
| |
| Jim Michaels 2006-02-23, 9:56 pm |
|
"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" <simon@webworx.co.uk> wrote in message
> news:M91Lf.69546$0N1.44349@newsfe5-win.ntli.net...
>
>
|
|
|
|
|