1 2 Previous Next 16 Replies Latest reply on May 1, 2012 7:24 PM by Gaff

    Is it Possible ?

    Venkadesh Raja
      Hi,

      Sorry for asking this silly question..i just wanted to know..


      Is it possible to get multiple table datas..without joining and without cartesian product..



      For eg,
        select d.dname,d.ename
        from emp e,dept d
        • 1. Re: Is it Possible ?
          sb92075
          Venkadesh wrote:
          Hi,

          Sorry for asking this silly question..i just wanted to know..


          Is it possible to get multiple table datas..without joining and without cartesian product..



          For eg,
          select d.dname,d.ename
          from emp e,dept d
          what result set do you expect/desire?
          • 2. Re: Is it Possible ?
            Venkadesh Raja
            Hi sb92075

            i just want to get username from one table and ename from another table ?
            • 3. Re: Is it Possible ?
              sb92075
              Venkadesh wrote:
              Hi sb92075

              i just want to get username from one table and ename from another table ?
              which username?
              which ename?

              what is relationship between username & ename?
              • 4. Re: Is it Possible ?
                rp0428
                >
                Is it possible to get multiple table datas..without joining and without cartesian product..
                >
                Sure - but not with your query; there is no ename in the DEPT table.

                Just query each table and make sure the query returns an identical result set in terms of the number of columns and datatypes.
                SELECT DEPTNO ID_NUM, DNAME MY_NAME FROM DEPT
                UNION ALL
                SELECT EMPNO, ENAME FROM EMP
                No joins and no cartesian products. You can use WHERE clauses for each query so that they return the number of rows you want.
                1 person found this helpful
                • 5. Re: Is it Possible ?
                  Venkadesh Raja
                  Hi sb92075 and rp0428



                  User detail table:-
                  select * from users;
                  
                  User_name
                  
                  aa
                  
                  bb
                  
                  cc
                  Employee Details Table:-

                  Select ename,empno from emp;
                  
                  Ename   Empno
                  ------------------------------
                  
                  Smith     1111
                  
                  Allen      2222
                  
                  Charles   3333
                  Here Username for Smith is aa


                  i want result like this
                  User_name  Ename
                  
                  aa             Smith
                  
                  bb            Allen
                  
                  cc            Charles
                  • 6. Re: Is it Possible ?
                    AdamMartin
                    I do not know where you are going with this, but I'll play along:
                    select users.user_name, emp.ename
                    from users, emp
                    where ((user_name = 'aa' and ename = 'Smith')
                        or (user_name = 'bb' and ename = 'Allen')
                        or (user_name = 'cc' and ename = 'Charles'))
                    1 person found this helpful
                    • 7. Re: Is it Possible ?
                      sb92075
                      Venkadesh wrote:
                      Hi sb92075 and rp0428



                      User detail table:-
                      select * from users;
                      
                      User_name
                      
                      aa
                      
                      bb
                      
                      cc
                      Employee Details Table:-

                      Select ename,empno from emp;
                      
                      Ename   Empno
                      ------------------------------
                      
                      Smith     1111
                      
                      Allen      2222
                      
                      Charles   3333
                      Here Username for Smith is aa


                      i want result like this
                      User_name  Ename
                      
                      aa             Smith
                      
                      bb            Allen
                      
                      cc            Charles
                      why does "aa" match with "Smith" and not with "Allen" or "Charles"?
                      • 8. Re: Is it Possible ?
                        Gaff
                        Then the answer to your question is "no". You have an implicit relation between username aa and ename Smith but it isn't reflected in your table definitions (foreign keys) and if it were, the way Oracle would link them up would be a join.

                        I'm guessing this is one of those situations where we haven't heard the real question yet. Why do you not want to do a join? It's what databases do and do quite well! Why is your data in Oracle instead of a text file or spreadsheet? How do you expect Oracle to connect in any way, aa with Smith?


                        Venkadesh wrote:
                        Hi sb92075 and rp0428



                        User detail table:-
                        select * from users;
                        
                        User_name
                        
                        aa
                        
                        bb
                        
                        cc
                        Employee Details Table:-

                        Select ename,empno from emp;
                        
                        Ename   Empno
                        ------------------------------
                        
                        Smith     1111
                        
                        Allen      2222
                        
                        Charles   3333
                        Here Username for Smith is aa <font color="red">How would anyone but you know this? More importantly, how would ORACLE know this?</font>


                        i want result like this
                        User_name  Ename
                        
                        aa             Smith
                        
                        bb            Allen
                        
                        cc            Charles
                        Edited by: Gaff on May 1, 2012 1:55 PM
                        • 9. Re: Is it Possible ?
                          Venkadesh Raja
                          Hi sb92075

                          Because User name for smith is aa.

                          Both the details are available for separate tables and no matching columns..i dono wht do now ?
                          • 10. Re: Is it Possible ?
                            Venkadesh Raja
                            Hi Gaff

                            i think it's not possible,but i just want to make ensure whether my thinking is correct or not,anyway thanks for your suggestions for make me sure.
                            • 11. Re: Is it Possible ?
                              sb92075
                              Venkadesh wrote:
                              Hi sb92075

                              Because User name for smith is aa.
                              How do you KNOW that user name for smith is aa?
                              Rows in a table are like balls in a basket & have NO inherent order.

                              can emp table have more rows than user table?
                              can user table have more rows than emp table?
                              1 person found this helpful
                              • 12. Re: Is it Possible ?
                                Gaff
                                Perhaps I've missed something. I see aa and Smith together in your RESULTs, but nothing in the source tables that indicates any relationship. You selected "*" from users, meaning every column. There was one returned, Username. That's where aa is. That's the only place aa is. In your mind, what tells Oracle that "aa is the username for Smith"? There is nothing. It's like I handed you a basket of puppies and said "Bring the one named Spot to me".
                                • 13. Re: Is it Possible ?
                                  Venkadesh Raja
                                  hi Gaff,

                                  Here i used two sample tables for understanding purpose.In real time requirement is when ever the user is getting data from one table they want to know which user is getting data,but i checked both tables there is no relationship between both tables.so i don't know ? How to do that? so i just simply told them Sorry i don't know.
                                  • 14. Re: Is it Possible ?
                                    William Robertson
                                    If you know for a fact that the ordering is significant and correct then yes, you could join them that way. It is a rather risky approach though, as the database does not guarantee to store or return rows in any particular order, even though loading rows into a new, empty heap-organised table will tend to preserve the order of insertion.
                                    1 person found this helpful
                                    1 2 Previous Next