Home » SQL & PL/SQL » SQL & PL/SQL » Want to display the data from two tables in SQL (11g R2.0)
Want to display the data from two tables in SQL [message #584368] Mon, 13 May 2013 07:20 Go to next message
srirams007
Messages: 7
Registered: May 2013
Location: Bangalore
Junior Member
In SQL...I want to display the manager details and the corresponding employee details and i donot want to use joins or co-related subqueries

I am trying it to use CASE or DECODE..

Here is the Query..

SELECT *
FROM (SELECT empno,
ename,
sal,
job,
CASE
WHEN empno IN (SELECT mgr FROM emp)
THEN
empno
|| ' '
|| RPAD (ename, 8, ' ')
|| ' '
|| LPAD (NVL (TO_CHAR (mgr), 'Manager'), 8, ' ')
|| ' '
|| LPAD (sal, 6)
|| ' '
|| NVL (comm, 0)
|| ' '
|| deptno
END
AS MGR
FROM emp)
WHERE mgr IS NOT NULL OR ename = 'KING'


The output is as follows..

7839 KING 5000 PRESIDENT 7839 KING Manager 5000 0 10
7698 BLAKE 2850 MANAGER 7698 BLAKE 7839 2850 0 30
7782 CLARK 2450 MANAGER 7782 CLARK 7839 2450 0 10
7566 JONES 2975 MANAGER 7566 JONES 7839 2975 0 20
7902 FORD 3000 ANALYST 7902 FORD 7566 3000 0 20
7788 SCOTT 3000 ANALYST 7788 SCOTT 7566 3000 0 20

I am able to display only the Manager details or employee details and not both at the same time..

Could any one of you please suggest the approach for the above scenario..

Thanks in Advance..
Re: Want to display the data from two tables in SQL [message #584371 is a reply to message #584368] Mon, 13 May 2013 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 60016
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query and result are unreadable. And what you want is not clear.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

If you don't know how to do it, learn it using SQL Formatter.

Regards
Michel

[Updated on: Mon, 13 May 2013 07:32]

Report message to a moderator

Re: Want to display the data from two tables in SQL [message #584381 is a reply to message #584371] Mon, 13 May 2013 08:33 Go to previous messageGo to next message
srirams007
Messages: 7
Registered: May 2013
Location: Bangalore
Junior Member
Thank You Michel..As this is my first post..i formatted in toad and pasted it in the Forum..

My Query is without using joins or subqueries can we join two tables(As in to display manager and employee details in single query)

My oracle version is 11.2.0.1

My formatted code is

SELECT *
FROM (SELECT empno,
ename,
sal,
job,
CASE WHEN empno IN (SELECT mgr FROM emp) THEN empno||' '||Ename END
AS EMP_DETS
FROM emp)
WHERE EMP_DETS IS NOT NULL

The output is

Empno   ename     sal        job       emp_dets 
7839    king     5000     president     7839-->KING
7698	BLAKE	2850	  MANAGER	7698-->BLAKE
7782	CLARK	2450	  MANAGER	7782-->CLARK

I can see only the manager details under EMP_DETS column..how can we display both employee and manager details
Re: Want to display the data from two tables in SQL [message #584386 is a reply to message #584381] Mon, 13 May 2013 09:11 Go to previous messageGo to next message
shaishuk
Messages: 1
Registered: May 2013
Location: India
Junior Member

Hi,

you can use below approach to get details of employee and departments.

Example:

CASE WHEN empno IN (SELECT mgr FROM emp) THEN empno||' '||Ename || ' '|| (select dept_name from dept where dept.mgr_id=emp.empno) END
AS EMP_DETS

The above code to simulate the solution of your issue.

Please let me know if it works for you.

Thanks in advance.
Re: Want to display the data from two tables in SQL [message #584387 is a reply to message #584381] Mon, 13 May 2013 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 60016
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the result you want. The sentence "display both employee and manager details" is not clear.
In addition, do NOT use scalar subquery in SELECT clause until you'll be an expert in SQL.
And FORMAT your query. Anyway you format it, you MUST check it BEFORE posting using the Preview button and if it is not formatted you MUST retry until you succeed and ONLY THEN you click on the Reply button.

Regards
Michel
Re: Want to display the data from two tables in SQL [message #584402 is a reply to message #584368] Tue, 14 May 2013 00:14 Go to previous messageGo to next message
rajiv.v
Messages: 16
Registered: February 2013
Location: Bangalore
Junior Member
I suppose you must try this ,am not too sure,had used this very long back and i cant test this piece of code at present as i have no access to the data base(New joiner)

select level,lpad(' ',2*(level-1))|| ename "employee",empno,mgr "manager" from emp
start with mgr is null
connect by prior empno=mgr;

I suppose michel ,roach or littlefoot to throw some light on this.
Re: Want to display the data from two tables in SQL [message #584404 is a reply to message #584402] Tue, 14 May 2013 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 60016
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We can throw some light when we'll know the result OP wants instead of posting result he doesn't want.

Regards
Michel
Re: Want to display the data from two tables in SQL [message #584456 is a reply to message #584404] Tue, 14 May 2013 08:41 Go to previous messageGo to next message
srirams007
Messages: 7
Registered: May 2013
Location: Bangalore
Junior Member
Thank you All..

I want to display the data in the following format without using joins(Data in emp table provided by oracle corporation)

MANAGER_ID	MANAGER_NAME	 MANAGER_JOB	EMPLOYEE_ID	EMPLOYEE_NAME	EMPLOYEE_JOB
7839	           KING	          PRESIDENT	7698	          BLAKE	          MANAGER
7839	           KING	          PRESIDENT	7782	          CLARK           MANAGER
7839	           KING	          PRESIDENT	7566	          JONES	          MANAGER
7698	           BLAKE	  MANAGER	7654	          MARTIN	  SALESMAN
7698	           BLAKE	  MANAGER	7499	          ALLEN	          SALESMAN
7698	           BLAKE	  MANAGER	7844	          TURNER	  SALESMAN
7698	           BLAKE	  MANAGER	7900	          JAMES	          CLERK
7698	           BLAKE	  MANAGER	7521	          WARD	          SALESMAN


The above output is generated by join only.But i want to know if there is any other approach to generate the above output other than using joins in SQL.

Thanks
Re: Want to display the data from two tables in SQL [message #584457 is a reply to message #584456] Tue, 14 May 2013 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 60016
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to use join?
How do you think you can avoid join? Think!

Regards
Michel
Re: Want to display the data from two tables in SQL [message #584469 is a reply to message #584457] Tue, 14 May 2013 09:37 Go to previous messageGo to next message
srirams007
Messages: 7
Registered: May 2013
Location: Bangalore
Junior Member
My thought is in PL/SQL we can implement the same scenario by using the PL/SQL records and PL/SQL tables without physically joining the tables.

And In the same way want to know any approach in SQL.

I have tried following approaches but not able to achieve the result

1.with co-related subqueries but able to display either Manager or Employee details at a time.
2.with scalar subqueries but able to display one col at a time(i can display manager details and only one col of employee details).
3.With Hierarchel queries displayed the hierarchey but not in the above format.

Re: Want to display the data from two tables in SQL [message #584471 is a reply to message #584469] Tue, 14 May 2013 09:46 Go to previous messageGo to next message
ThomasG
Messages: 3123
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could of course print out the result of two separate queries and use a scissor and glue to get the expected result.

If you "do something in PL/SQL" you will just wind up implementing something that does the same thing a join does. Since what you want to do logically IS a join. If you do it you WILL "use a join" even if it isn't the already built-in one.

So WHY don't you want to use a join?

[Updated on: Tue, 14 May 2013 09:47]

Report message to a moderator

Re: Want to display the data from two tables in SQL [message #584552 is a reply to message #584471] Wed, 15 May 2013 06:07 Go to previous messageGo to next message
srirams007
Messages: 7
Registered: May 2013
Location: Bangalore
Junior Member
Ok..Thank you Thomas

I am not against joins or something.
Just want to know if there is any other approach in oracle SQL for displaying the same result as above apart from joins.
So that can have more idea on oracle concepts.

Thanks Once again.
Re: Want to display the data from two tables in SQL [message #584566 is a reply to message #584552] Wed, 15 May 2013 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 60016
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not Oracle concepts it is SQL concepts.
Just think what you want to do and you will see it is, anyway, a join because what you specify as result is simply a join.

Regards
Michel

[Updated on: Wed, 15 May 2013 10:30]

Report message to a moderator

Re: Want to display the data from two tables in SQL [message #584617 is a reply to message #584566] Thu, 16 May 2013 05:12 Go to previous message
srirams007
Messages: 7
Registered: May 2013
Location: Bangalore
Junior Member
Ok..Thank you
Previous Topic: substr of clob
Next Topic: Please provide real time scenario for when we use object type in PL/SQL block
Goto Forum:
  


Current Time: Mon Dec 22 06:05:14 CST 2014

Total time taken to generate the page: 0.18819 seconds