sql syntax

I presume x is the schema name owning the tables "sale", "item" and "department" ?

something like this ...

select i itemname 
 from x.item i
 where exists ( select null
      from x.sale s
      join x.department d
        on s.dname = d.deptname 
       and d.deptfloor = 2
      where x.itemno = i.itemno 
  )  

Select allOpen in new window

why can't I use as?

can you point out the error in my original query?

you can use "as", but the EXISTS is likely to give better performance.

how do you run the query? in pl/sql possibly?

then, you have to know that you cannot run SELECT like that inside pl/sql.

sql++

Is this what you are trying to do?

~bp

SELECT itemname
FROM   x.item i,
       x.sale s,
       x.department d
WHERE  s.dname = d.deptname
AND    d.deptfloor = 2
AND    i.itemno = s.itemno; 

Select allOpen in new window

ok, but as pl/sql block, or simply the "SELECT" as such?

your querying method seems a bit different with mine.. I never used select i itemno, I always use select itemno

you should use table aliases all the time, as it makes it 100% clear from which table the column is coming from.
when you have to read the query later, you don't have to look up the table column design to know, you can just read it from the query.

angelll I have no idea what a table aliases is? is it the as?

@kuntilanak,
I think, based upon the aliased able, that the statement shouldhave started
SELECT i.itemno
I.e., a period between the "i" and he "iemno".

if you're referring to the as, yes that is my main problem.. never been successfull using as

select itemname from x.item, (select itemno from x.sale, x.department where dname=deptname and deptfloor = 2 as t) where item.itemno = t.itemno;

Select allOpen in new window

AngelIII's  statement - I presume x is the schema name owning the tables "sale", "item" and "department" ?

kuntilanak  - Can you confirm this?

that is true