6 Replies Latest reply: Jan 5, 2013 3:36 AM by moreajays RSS

    Query giving many records

    951027
      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
          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
            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
              any suggestion guys???


              Thanks
              • 4. Re: Query giving many records
                Girish Sharma
                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
                  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
                    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