Subquery on SELECT clause 
Author Message
 Subquery on SELECT clause
I am trying to retrieve a description value from a table while querying
another but I can't  get the SQL text
right. For example, I need the order_id and the employee name instead of its
id.

SELECT order_id,
(SELECT emp_name FROM Employees WHERE Employees.emp_id = Orders.emp_id) AS
"Name"
FROM Orders
WHERE order_Item = 'soap'

The SQL works fine in SQL Server or Access, with minor modifications.

Can you help ?


Thanks



Fri, 18 May 2001 03:00:00 GMT
 Subquery on SELECT clause


(if that email address didn't require changing)

Quote:

>I am trying to retrieve a description value from a table while querying
>another but I can't  get the SQL text
>right. For example, I need the order_id and the employee name instead of its
>id.

>SELECT order_id,
>(SELECT emp_name FROM Employees WHERE Employees.emp_id = Orders.emp_id) AS
>"Name"
>FROM Orders
>WHERE order_Item = 'soap'

>The SQL works fine in SQL Server or Access, with minor modifications.

this would simply be:

select order_id, emp_name "Name"
from orders, employees
where employees.emp_id = orders.emp_id
and order_item = 'soap'

Quote:
>Can you help ?


>Thanks

Thomas Kyte

Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.



Fri, 18 May 2001 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Using Union subquery in select clause

2. Subqueries in select clause

3. ANSI syntax for a correlated subquery in the SELECT clause

4. Outer reference in SELECT clause of subquery

5. results from SELECT clause used in an IN clause

6. Is it possible to use a SELECT clause in a FROM clause

7. subquery in the from clause?

8. Subqueries Fields in WHERE clause

9. Subquery in FROM clause allowed?

10. Where Clause in Subquery not working

11. Subqueries in From clause

12. Subquery in the FROM clause


 
Powered by phpBB® Forum Software