Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Posts
    3

    Unanswered: Querying with field having multiple values with coma separated

    Hi,
    Here is scenario where I need to join two tables and get results as illustrated:

    I have two tables: where T1 is not normalised

    T1:

    Column1 Column2
    ======= =======
    1 A
    2 A, B, C
    3 A, B
    4 C
    5 A, D

    T2:
    Column1
    =======
    B
    C

    I need a query that would return the following

    Cloumn1 Column2
    ======= =======
    2 A, B, C
    3 A, B
    4 C


    Any assistance with this problem would be greatly appreciated.
    -Dharanendra

  2. #2
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137
    select T1.column1,T1.column2 from T1, T2
    where
    T1.column2 like '%' || T2.column1 || '%'

  3. #3
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137
    select distinct T1.column1,T1.column2 from T1, T2
    where
    T1.column2 like '%' || T2.column1 || '%'

  4. #4
    Join Date
    Apr 2006
    Posts
    3
    Hi,
    Thanks for your answer.

    This query fetched records #6, with value BB. But I don't want record #6

    T1:

    Column1 Column2
    ======= =======
    1 A
    2 A, B, C
    3 A, B
    4 C
    5 A, D
    6 BB

    T2:
    Column1
    =======
    B
    C

    I need a query that would return the following

    Cloumn1 Column2
    ======= =======
    2 A, B, C
    3 A, B
    4 C

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select T1.column1
         , T1.column2 
      from T1 
    inner
      join T2
        on ','||T1.column2||','
     like '%,'||T2.column1||',%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If your T1 table gets big you will have serious performance problems if you use a csv list for a join condition. It would be much better to normalise the data into seperate rows as we had a similar thing in one of our schemas and it completely killed performance.

    Alan

Posting Permissions

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