Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    Unanswered: SELECT with REPLACE!?!?

    Hi, I have 2 tables... in one, I have a filed that is a text type and I store a list of indexes from the other table (one per line).
    TABLE A (fields: ..........., SECTORES,........)
    TABLE B (fields: id_sector, name)
    So when a make a query to, the field SECTORES has
    "1
    2
    3
    4"
    But I want it to show the name that corresponds to that index. I can't doit via a join o WHERE because SECTORES its not a field with 1 value but a list of values...
    I was trying to do it with REPLACE and SUBSTRING_INDEX..... but I can't figure it out ......
    any ideas???
    thanks!

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    Change the structure of your database. Having a list of values in a field violates First Normal Form of database normalization.

  3. #3
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    yes....... but

    I was thinking on doind that.... but the problem is how do I represent the list? I can't add a field per each one because it's not a fixed number... maybe with only 1 field pointing to a table that represents the list?
    thanks again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, the A table does not point to the table that represents the list, the table that represents the list points to the A table --

    create table TABLEA
    ( Aid integer not null primary key
    , name varchar(55)
    , ...
    );
    create table TABLEB
    ( id_sector integer not null primary key
    , name varchar(55)
    );
    create table TABLEAB
    ( Aid integer not null
    , id_sector integer not null
    , primary key ( Aid, id_sector )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    yes!

    Yes, I see how that could work, thanks very much for pointing that to me.
    (I supose the third table is table C)
    I let you know if it went alright.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by diegolaz
    (I supose the third table is table C)
    yes, i called it AB, to emphasize that it is a relationship table between A and B

    you could also call it SECTORES

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •