UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Trouble With Subquery In A Where Statement, MySQL 5.0    
 
   
PaulinRhody
post Apr 30 2016, 06:09 PM
Post#1



Posts: 113
Joined: 5-December 13
From: Rhode Island


I'm working through an assignment to prepare for an exam, and I'm stuck. The problem is:

Find and list all employees whose department sells toys with a retail cost of more than $25.00.
Use a subquery to find all departments with r_cost > 25.00.
Your output should include employee full name (combine first & last name) and the department name.
Hint: you'll need a join between employee and department to get the department name; use this and a subquery finding all toys with r_cost >25.00.

This is what I start with, but I can't figure out how to get to one value for the subquery:

SELECT concat(e.fname, " ", e.lname) AS FullName, d.d_name
FROM employees e, departments d
WHERE e.department = d.d_id AND d.d_id = (

SELECT t.dept
FROM toys t, departments d
WHERE t.dept = d.d_id AND t.r_cost>25
);

Any ideas?
Go to the top of the page
 
Doug Steele
post Apr 30 2016, 07:13 PM
Post#2


UtterAccess VIP
Posts: 21,497
Joined: 8-January 07
From: St. Catharines, ON (Canada)


To be honest, I'm not sure you can assume that your subquery will always only return one row.

Why not try replacing the equal sign with the IN keyword, as in

CODE
SELECT concat(e.fname, " ", e.lname) AS FullName, d.d_name
FROM employees e, departments d
WHERE e.department = d.d_id AND d.d_id IN (
  SELECT t.dept
  FROM toys t, departments d
  WHERE t.dept = d.d_id AND t.r_cost>25
);
Go to the top of the page
 
PaulinRhody
post Apr 30 2016, 08:10 PM
Post#3



Posts: 113
Joined: 5-December 13
From: Rhode Island


Thanks for the help Doug.

That works. The instructor for my class made a statement on a video for our class, and I'm kind of confused about the second part. I'll plan to think on it, but while I'm thanking you, I'll run this by you to see if you can either correct his statement or my thinking. He said, "With sub queries there are always two rules you need to remember. Number 1 is that the innermost query is always executed first. Number 2 is that the innermost query must always pass a single value."

Paul
Go to the top of the page
 
Doug Steele
post May 1 2016, 06:28 AM
Post#4


UtterAccess VIP
Posts: 21,497
Joined: 8-January 07
From: St. Catharines, ON (Canada)


In my opinion, your instructor is wrong.

There are three distinct classes of subqueries:
  1. Table Subqueries return an entire set of data, including multiple columns and rows. They can be used anywhere you can use the name of a table or a view, or a stored procedure or function that returns a table.
  2. Table Subqueries with only one column return only a single column with multiple rows. They can be used anywhere you can use a Table Subquery, or they can be used as the list of values to be compared in an IN predicate.
  3. Scalar Subqueries return only one value. They can be used anywhere you could otherwise use a column name or expression on column names.

And I'd also argue with the statement that "the innermost query is always executed first". With correlated subqueries (which use one or more filters in either a WHERE or HAVING clause that depends on a value provided by the outer query), the subquery is “co-related” to the outer query, and your database engine must run the subquery once for every row returned by the outer query.

Perhaps he uses other terminology for these situations, but to me, they're all examples of subqueries.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:57 AM