SQL SUBQUERY

Using the Northwind Database, write a sub-query to find all products that have never been ordered.
Show the product Id and the product name in your results.
mnoisetteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
Heh. Homework.

Hint: use LEFT JOIN.
Mike EghtebasDatabase and Application DeveloperCommented:
You don't need sub-query, just a query where there are two tables Products and Order Details. So, with left join Order Details table will have null value (Where o.ProductID is null).

Also, you need to use Distinct keyword to eliminate repeats. But, if you have to use sub-query for some reason, try "2. Correlated subqueries".

Mike
Jeff DarlingDeveloper AnalystCommented:
I think this may be a trick question.  In the Northwind database I have, there are no products that have not been ordered.

SELECT productid
	,productname
FROM products
WHERE productid NOT IN (
		SELECT DISTINCT productid
		FROM [Order Details]
		)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mnoisetteAuthor Commented:
This is a trick question and I do know that I could have written the query using an Outer Join, but they wanted it done with a subquery.
Mike EghtebasDatabase and Application DeveloperCommented:
Jeff has the solution for you. If you want more explanation on the five types of subqueries, see the link I have included in my last post. I also found out all products has been ordered in my version of northwind database.

Mike
mnoisetteAuthor Commented:
Thanks dude!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.