If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > PostgreSQL > how to join using comma seperated column?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,807
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On