Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2013
    Posts
    1

    Unanswered: how to display matching and non matchings records in sybase

    Hi,

    table A
    -------
    XYZ

    table B
    -------
    XYZ
    AXYZ

    I want to display both the records from table B as it matches to table A, first character "A" is added for random records and matches to table B.

    I tried below:

    Select a.Id, b.Id
    From table a
    LEFT OUTER JOIN table b on a.Number like 'A%'+ b.code
    UNION
    Select a.Id, b.Id
    RIGHT OUTER JOIN table b
    ON a.Number=b.code
    Last edited by sybasequery; 12-22-13 at 01:44.

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hi,

    This is what i came up with. Try it to see if applies.

    Code:
    create table A (id int, code varchar(5))
    create table B (id int, code varchar(5))
    
    insert into A values (1,'XYZ')
    insert into B values (2,'XYZ')
    insert into B values (3,'AXYZ')
    
    select distinct case when charindex(A.code, B.code) is not null then B.id end from B,A
    PS: I tested the code in mysql and adapt to the charindex function.
    Also this is a quick fix, because it presents duplicate records that we "eliminate" with the disticnt, beacuse the tables are not joined making a cartesian product.
    To get the non mactching results just change to null.
    Last edited by Catarrunas; 01-10-14 at 11:20.

Posting Permissions

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