Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    4

    Unanswered: can this sql work correctly?

    I want to get the records from table2 and table1 that table2.id2(string) include table1.id(string),means table1.id is a substring in table2.id2,can this sql work correctly? Thanks!


    SELECT table1.abc, table1.id, table2.id2, table2.ddd
    FROM table2 INNER JOIN table1 ON table2.id2 like table1.id

  2. #2
    Join Date
    May 2002
    Location
    KOLKATA, INDIA
    Posts
    7
    I am sure this will work.

    SQL> select * from a;

    C1 C2
    ---------- --------------------
    AAAA santosh sarkar
    AAAB debasish datta
    AAAC sujata agarwal
    AAAD rina ray

    SQL> select * from b
    2 ;

    C3 C4
    ---------- --------------------
    XXXX santosh
    XXXY debasish
    XXXZ agarwa

    SQL>


    select a.*, b.*
    from a, b
    where substr(a.C2, instr(a.c2,b.c4), length(b.c4)) = b.c4
    SQL> /

    C1 C2 C3 C4
    ---------- -------------------- ---------- --------------------
    AAAA santosh sarkar XXXX santosh
    AAAB debasish datta XXXY debasish
    AAAC sujata agarwal XXXZ agarwa

    SQL>



    Santosh Sarkar

  3. #3
    Join Date
    May 2002
    Posts
    15

    Smile

    tshm, you almost got the query right! You only forgot to put the wildcards. This will return all rows where table1.id is found in table2.id2.

    SELECT table1.abc, table1.id, table2.id2, table2.ddd
    FROM table2 INNER JOIN table1 ON table2.id2 like '%'+table1.id+'%'

Posting Permissions

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