Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    2

    Unanswered: Is it possible to write a query for this ?

    Hi,

    I have 2 database tables:

    Code:
    Table A
    ID    LOCATIONS        More columns
    id1   loc1,loc2,loc3
    id2   loc2,loc4,loc5,loc7
    id3   loc2,loc5
    Code:
    Table B
    ID    NAME            More Columns
    loc1  NameA
    loc2  NameB
    loc3  NameC
    loc4  NameD
    loc5  NameE
    loc6  NameF
    loc7  NameG
    loc8  NameH
    Every item in the comma separeted list in row LOCATIONS of the table A
    is a key in the row ID of the table B

    My problem is, that I need a SQL or PL/SQL query, which produce
    the result:
    Code:
    ID    LOCATIONS
    id1   NameA,NameB,Name3
    id2   NameB,NameD,NameE,NameG
    id3   NameB,NameE
    Has someone an idea ?

    Thanks for you help

    PS.: The best solution is to redesign the whole database model, but this is
    not an option.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to oracle forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    william, that's a lovely page, i'll be linking to it in an article i'm writing next week (see http://r937.com/sqlate.cfm)

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

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    One issue was not addressed. having a number of keys stored in a single varchar2 field is a bad database design. You should be using three tables


    Code:
    Table A person_ID  More columns 
    
    Table B location_ID NAME More Columns 
    
    Table C person_ID location_id
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jun 2006
    Posts
    2
    I know that this is a bad design, but it is too expensive to change the design.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by heikow
    I know that this is a bad design, but it is too expensive to change the design.
    i think you will find that in the long run it is too expensive to stay with that design, and the sooner you change it, the less it will hurt you

    as for your immediate problem -- write a program

    you need to read both tables into memory -- if they'll fit!! -- and do the matching yourself

    easy, right?

    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
  •