Darren O'BRIEN Darren O'BRIEN - 1 year ago 74
MySQL Question

Difficulty understanding simple sub-queries


I have been asked to return some specific recordsets using sub-queries however I find myself having difficulty understanding how to use sub-queries to get specific results.

E.x. Using a sub-query, list the productName(s) from the products table for orderNumber 10121 from the orderdetails table.

I attempted:

SELECT productName
FROM products
WHERE (SELECT orderNumber FROM orderdetails WHERE orderNumber = 10121);

but apparently I have missed something.

Any help would be appreciated.

Answer Source

You are very close. It always helps me to view the subquery as returning an array of objects you will check. So for this example, you probably have a productNumber in both of these tables to make them relational. Therefore, your SQL would be something like:

SELECT productName FROM products WHERE productNumber IN (SELECT productNumber FROM orderdetails WHERE orderNumber = 10121);

This subquery will get all of the productNumbers for this order, and then your query will grab the product names for all of these products!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download