#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    34
    Rep Power
    11

    select possible?


    ihello,

    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
    john,1,1
    martin,1,2
    richard,3,3
    joseph,2,3

    hope its clear enough
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    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 10:33 AM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    34
    Rep Power
    11
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    ok, your design suffers from some normalization problems. In order to do this, you should have maybe

    table1eople
    CREATE TABLE people (person VARCHAR,
    cities_key INT4
    FOREIGN KEY cities_key REFERENCES cities (key))

    and then each city key could have a few cities, let's do five.
    table2:cities

    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:

    table3:name_lookup

    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.

IMN logo majestic logo threadwatch logo seochat tools logo