Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010
    Posts
    3

    how to join using comma seperated column?

    i want to join two tables using common column, but in one table the column is having comma seperated values how can i join those tables?
    i try with like operator but not got exactly. please tell me the solution

    the master table name is 'languages' and second table is 'pepople'

    Tbale 1 : languages (id int,desc varchar)
    --------------------------------
    lang_id | lang_desc
    ---------------------
    1 | English
    2 | Telugu
    3 | Hindi



    Table 2 : peopele (id int,name varchar,lang_id varchar)
    ------------------------------------------------------
    id | name | lang_id
    ----------------------------
    1 | Apple | 1,2
    2 | Grape | 3,1


    i try with

    Code:
    select p.id,p.name,l.lang_id,l.lang_desc
    from langages l join people p on p.lang_id=l.lang_id
    but this query returned zero records
    and second trial is:

    Code:
    select p.id,p.name,l.lang_id,l.lang_desc
    from langages l join people p
    on l.lag_id::varchar like '%,'+p.lang_id::varchar+',%' 
    
    this query given ERROR: operator does not exist: unknown + character varying
    i actually need result as below

    id | name | lang_id | lang_desc
    ------------------------------------------
    1 | Apple | 1 | English
    1 | Apple | 2 | Telugu
    2 | Grape | 3 | Hindi
    2 | Grape | 1 | English


    Please tell the way to do this

  2. #2
    Join Date
    Nov 2003
    Posts
    2,815
    The correct answer is: Normalize your tables. Storing comma separated values is almost always a bad idea.

    The usual reaction to that recommendation is: "I can't change the design, am I'm not responsible for it".

    So if you really, really have to keep this broken design and want to fight symptons rather than fixing the cause of the problem you could do something like this:

    Code:
    select p.id, p.name, l.id, l.descr
    from languages l
      join (
        select id, name, regexp_split_to_table(lang_id, ',') as lid
        from people
      ) p on p.lid::integer = l.id
    A much better and cleaner solution however is to add a table linking people to languages:

    Code:
    create table languages 
    (
      id      int not null primary key,
      descr   varchar
    );
    
    create table people 
    (
      id     int not null primary key,
      name   varchar
    );
    
    create table speaks 
    (
       person_id int not null references people, 
       lang_id int not null references languages,
       primary key (person_id, lang_id)
    );
    
    insert into languages 
    values
    (1,'English'),
    (2,'Telugu'),
    (3,'Hindi');
    
    insert into people 
    values
    (1,'Apple'),
    (2,'Grape');
    
    insert into speaks (person_id, lang_id)
    values
    (1, 1),
    (1, 2),
    (2, 3),
    (2, 1);
    
    select p.id, p.name, l.id, l.descr
    from people p
      join speaks s on s.person_id = p.id
      join languages l on l.id = s.lang_id;
    (Note that I changed the colum desc to descr because desc is a reserved word and should not be used as a column name)


    This solution has several advantages:

    • You can never a language that doesn't exist
    • Your statement does not fail if someone enters "foobar" into the lang_id column (of your original table)
    • You can ensure that the same language is only assigned once to one person
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Dec 2010
    Posts
    3

    how to join using comma seperated column?

    thank you for your valuable suggestion, i will go through normalized form

Posting Permissions

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