SQL oracle query

kuntilanak
kuntilanak used Ask the Experts™
on
I am quite a noob in SQL query, what is wrong with the following:


select itemno fromsale where dname = (select deptfloor from department where deptfloor = 2).deptname;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

Author

Commented:
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);

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Kindly try this one out
select itemno 
from sale where dname in (select deptname from department where deptfloor = 2)

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
and I got the following error after your fix

ERROR at line 1:
ORA-01722: invalid number

Author

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

Commented:
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!

Author

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

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

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

Author

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

Author

Commented:
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?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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);
x77
Commented:
select itemno from Sale where dname in
    (select deptname from department where deptfloor = 2);
 This Option Grants unique rows from Sale .

If you use:
 Select S.itemno from sale S,  department  D where S.dname=D.deptname and D.DeptFloor=2;
This Returns n x m Rows if Dname occurs m times.

Author

Commented:
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);

Author

Commented:
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

Author

Commented:
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;

Open in new window

Author

Commented:
how can I turn this query into nested selects:


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

Open in new window

x77

Commented:
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;

Author

Commented:
how about nested select statements how can I change it?
Data Engineer
Commented:
Are you still looking for solution?

When you run your quey, what is the error message you are getting?

select itemno from sale join (select deptfloor, deptname from mccann.department where deptfloor = 2) as a on (dname = a.deptname);
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
          );

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Try this one:
select itemno from sale join (select deptfloor, deptname from mccann.department where deptfloor = 2) a on (dname = a.deptname);
 
or
 
select itemno 
from sale join mccann.department a on (dname = a.deptname)
where a.deptfloor = 2

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial