October 6th, 2003, 09:52 AM
is it possible to do such thing?
I have 1 table with columns (person text,county int2,city int2)
in the second table are columns (id_city int2,name_city text)
i'd like to do an select which will do something like this:
(county is just one of cities)
person, name_of_county , name_of county
hope its clear enough
October 6th, 2003, 11:27 AM
sorry I am having some trouble interpreting your problem, but I am on some cold medicine which is not helping...
I think you have a table with references to another; if you have set up with referential integrity as a must (as with a foreign key) then you would simply have to do
SELECT person, city_name, county_name
FROM first_table, second_table
WHERE first_table.county_id = second_table.county_id
***errr I think you wanted the ID's not the names, so it would just be SELECT person, city_id, county_id...
something like that. Does that make sense, or is what you are trying to achieve more complicated than the query I have written?
Last edited by metaBarf; October 6th, 2003 at 11:33 AM.
October 6th, 2003, 11:39 AM
you didn't get it
i'll put it another way.
int the 1st table, there will be columns person,id_city1,id_city2
in the second table are columns id_city,name_of_city
now i want to get person, nameof_city1,nameof_city2
thx for help
October 6th, 2003, 02:47 PM
ok, your design suffers from some normalization problems. In order to do this, you should have maybe
CREATE TABLE people (person VARCHAR,
FOREIGN KEY cities_key REFERENCES cities (key))
and then each city key could have a few cities, let's do five.
CREATE TABLE cities (key INT4, city1 INT4, city2 INT4, city3, INT4, city4 INT4, city5 INT4)
and then each city column's number key can reference to a name in another table:
CREATE TABLE name_lookup (city_key INT4, city_name VARCHAR)
I need to look at this again when I have a clear head, but there is something not correct with the design. If you normalize you could do an easy select statement. Something's not jelling for me on making the correct SELECT statement, but this may get you started for a little bit. Sorry I will reply more when I get a moment.