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

    Unanswered: 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

    from langages l join people p on p.lang_id=l.lang_id
    but this query returned zero records
    and second trial is:

    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
    Provided Answers: 23
    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:

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

    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 
    insert into people 
    insert into speaks (person_id, lang_id)
    (1, 1),
    (1, 2),
    (2, 3),
    (2, 1);
    select,,, l.descr
    from people p
      join speaks s on s.person_id =
      join languages l on = 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:

    Tips for good questions:

  3. #3
    Join Date
    Dec 2010

    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