0
SELECT ENAME, EMP.JOB,DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.JOB=DEPT.DNAME;

This is giving the output as "no rows selected".

Xavi López
  • 27,550
  • 11
  • 97
  • 161
Chay
  • 11
  • 8
  • 2
    Some information about your table structure? Should EMP.JOB even equal DEPT>DNAME? – Matthew Feb 06 '13 at 18:11
  • Yes ! i think in DEPT>DNAME you have misplaced > by " ." .... anyhow i got it thank you Very much – Chay Feb 06 '13 at 18:27

2 Answers2

1

Assuming that EMP.JOB=DEPT.DNAME is right (something that i can not imagine) your query would be actually right, and you can join this like:

SELECT ENAME, EMP.JOB,DEPT.DNAME
FROM EMP inner join DEPT
ON EMP.JOB=DEPT.DNAME;

But this would'nt give other esults than your query, i guess the problem is that your confusing columns, usually references are done through an id column.

The Idea that EMP.JOB which might be developer or programmer for example would match a DEPT.DNAME which in this case would be development department for example is somehow wierd, i guess you take a second look at how your tables are connected with each other.

Generating a visual diagram from your database could be helpful, take alook at this thread fro further information: Tools to generate a database diagram/ER diagram from existing Oracle database?

Community
  • 1
  • 1
CloudyMarble
  • 36,908
  • 70
  • 97
  • 130
-1

I'm not sure what you want - the job will never be equal to dept name... Here's the correct join -copy/paste to run and see results:

SELECT d.deptno, ENAME, JOB, DNAME 
  FROM scott.EMP e, scott.DEPT d
 WHERE e.deptno = d.deptno
ORDER BY d.deptno
/

FYI: The EMP.JOB=DEPT.DNAME will never be correct as in orig question/query.

CloudyMarble
  • 36,908
  • 70
  • 97
  • 130
Art
  • 5,616
  • 1
  • 20
  • 22
  • Thank you for your help I know the ans which you have given but the question i asked is correct. For example: In Emp i have Job called Salesman In Dept table i have Salesman in Dname column. So I want to join them ... anyhow I got it Thank you Sir! – Chay Feb 06 '13 at 18:24
  • @Art Your asnwers assumes there are deptno column on both tables which is absolutly logical but this doesnt make it to a fact, in my opinion this is still no reason to downvote the answer since the idea is still correct. Anyway since we have too many downvoters on this site ill give it +1 to Compensate the downvote. – CloudyMarble Feb 07 '13 at 06:07
  • @MeNoMore - You are right. I wrote that I assumed user talks about scott.emp table and not his table. Whoever downvote this probably can read minds of others and know nothing about SQL. – Art Feb 07 '13 at 13:16