SQL oracle query

Try now
select itemno from sale where dname = (select deptfloor from department where deptfloor = 2);

how about this one

the as seems not to work

select itemno from sale join (select deptfloor from mccann.department where deptfloor = 2) as a on (dname = a.deptname);

Select allOpen in new window

Kindly try this one out

select itemno 
from sale where dname in (select deptname from department where deptfloor = 2)

Select allOpen in new window

and I got the following error after your fix

ERROR at line 1:
ORA-01722: invalid number

I want to do this using join as on my second post

Table relations and which to tables? Because joins are done like this

SELECT a.id FROM table a JOIN table2 b ON a.id = b.id

But keep in mind that is just an example!

isn't that what I had in my second post? What's wrong with my second post?

Well if you are getting an error something is wrong :)

But your second post is selecting the column not the whole table

seems to be a problem with the as keyword on my second query

select is selecting a column right? and yes itemno is a column and I am selecting that column from a table called sale... so what is wrong with that?

select itemno from sale join (select deptfloor from mccann.department where deptfloor = 2) as a on (dname = a.deptname);

Small Mistake here

select itemno from sale join (select deptfloor, deptname from mccann.department where deptfloor = 2) as a on (dname = a.deptname);

this is still wrong in the as:

select itemno from sale join (select deptfloor, deptname from mccann.department where deptfloor = 2) as a on (dname = a.deptname);

this query it self when executed is right:

select deptfloor from mccann.department where deptfloor = 2

however when i combine it with all the others, it doesn't work

this works but it doesn't use join... I wan't to use join

 Select S.itemno from sale S,  department  D where S.dname=D.deptname and D.DeptFloor=2;

Select allOpen in new window

how can I turn this query into nested selects:

select itemno from x.sale , x.department  where dname=deptname and deptfloor = 2;

Select allOpen in new window

Select S.itemno from sale S,  department  D where S.dname=D.deptname and D.DeptFloor=2;
This is a Join;
I Select this form of Join - It is more simple understand.
Performance is identical as with "Join" clause.
I Work with Oracle databases since 1987.

Select S.itemno from sale S Join Departament D on (S.dname=D.deptname ) Where D.DeptFloor=2;

how about nested select statements how can I change it?

Try this.
Its coorelated subquery (nested query):

select itemno 
from   x.sale s 
where exists
          (select 1 
           from x.department d 
           where s.dname=d.deptname 
           and d.deptfloor = 2
          );

Select allOpen in new window