This discussion is archived
6 Replies Latest reply: Jan 5, 2013 1:36 AM by moreajays RSS

Query giving many records

951027 Newbie
Currently Being Moderated
Hello all,

I need a help on his query,

select a.name,a.id from table1 a, table2 b , table3 c
where ( a.id=b.id or a.id=c.id) and a.name like 'xyz';

actually there are only 20 records but while im trying this its giving too many records like 1000's repeating


plz suggest


Thanks
  • 1. Re: Query giving many records
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    try this or provide sample data.
    select a.name,a.id from table1 a, table2 b , table3 c
    where a.id=b.id 
    and  a.id=c.id 
    and a.name like 'xyz%';
  • 2. Re: Query giving many records
    951027 Newbie
    Currently Being Moderated
    no this is getting no results, bcoz it should a.id should exisst either in table2 or table3


    example
    table1:
    
    Id     name                 
    1     Hansen     
    2     Svendson                
    3     Pettersen                 
    
    
    table2 
    
    Id     OrderNo     
    1     77895     
    5     34764     
    
    table3 
    
    id      orderno
    2     44678     
    3     22456     
    4     24562     
  • 3. Re: Query giving many records
    951027 Newbie
    Currently Being Moderated
    any suggestion guys???


    Thanks
  • 4. Re: Query giving many records
    Girish Sharma Guru
    Currently Being Moderated
    Something like this ?
    select a.name,a.id,b.orderno
    from table1 a
    join table2 b on a.id=b.id
    union all
    select a.name,a.id,b.orderno
    from table1 a
    join table3 b on a.id=b.id
    /
    
    NAME                                   ID    ORDERNO
    ------------------------------ ---------- ----------
    Hansen                                  1      77895
    Svendson                                2      44678
    Pettersen                               3      22456
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  select z.* from
      2  (
      3  select a.name,a.id,b.orderno
      4  from table1 a
      5  join table2 b on a.id=b.id
      6  union all
      7  select a.name,a.id,b.orderno
      8  from table1 a
      9  join table3 b on a.id=b.id
     10  ) z
     11* where z.name like 'S%'
    SQL> /
    
    NAME                                   ID    ORDERNO
    ------------------------------ ---------- ----------
    Svendson                                2      44678
    
    SQL>
    Regards
    Girish Sharma
  • 5. Re: Query giving many records
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,
    SQL>
    SQL> select id from (
      2  select a.id from table1 a,table2 b
      3  where a.id=b.id);
    
            ID
    ----------
             1
    
    SQL>
    SQL> select a.id,a.name from table1 a,table2 b, table3 c
      2  where a.id=b.id or
      3  a.id=c.id;
    
            ID NAME
    ---------- ----------
             2 Svendson
             2 Svendson
             3 Pettersen
             3 Pettersen
             1 Hansen
             1 Hansen
             1 Hansen
    
    7 rows selected.
    
    SQL>
    SQL> select distinct a.id,a.name from table1 a,table2 b, table3 c
      2  where a.id=b.id or
      3  a.id=c.id;
    
            ID NAME
    ---------- ----------
             1 Hansen
             2 Svendson
             3 Pettersen
    
    SQL>
    SQL> select distinct a.id,a.name from table1 a,table2 b, table3 c
      2  where a.id=b.id or
      3  a.id=c.id and a.name like '%HA%';
    
            ID NAME
    ---------- ----------
             1 Hansen
    Edited by: Chanchal Wankhade on Jan 4, 2013 10:59 PM
  • 6. Re: Query giving many records
    moreajays Pro
    Currently Being Moderated
    hi,

    Try this
    select a.name,a.id from table1 a, table2 b , table3 c
    where ( a.id(+)=b.id and a.id(+)=c.id) and a.name like 'xyz';
    or

    select a.name,a.id from table1 a, table2 b , table3 c
    where ( a.id=b.id(+) and a.id=c.id(+)) and a.name like 'xyz';
    Thanks,
    Ajay More
    http://moreajays.blogspot.com

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points