2

I have the following three tables:

  • Product
  • Purchase (contains ProductID as foreign key)
  • Sale (contain ProductID as foreign key as well)

I want to form query joining these 3 tables where my result is the product name, purchased, and sold.

Product Name - (from Products table)

Purchased - (Number of occurences in Purchase table according to ProductID)

Sold - (Number of occurences in Sale Table according to ProductID)

Can you please set me on the right track by giving me hints and I'll complete by myself?

Kyle Williamson
  • 2,251
  • 6
  • 43
  • 75
Hussein Yassine
  • 53
  • 1
  • 1
  • 6
  • https://technet.microsoft.com/en-us/library/ms191472(v=sql.105).aspx – saarrrr Mar 04 '16 at 20:52
  • 1
    Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. – Juan Carlos Oropeza Mar 04 '16 at 20:53
  • 1
    it would be more beneficial to you if you had tried and came to us when you got stuck, but here's a hint: `SQL INNER JOIN` – yardie Mar 04 '16 at 20:53

2 Answers2

24

I'm betting this will get deleted...but hopefully you see this before it does. The following is really helpful in understanding the differences in the SQL JOINS. SQL JOINS. This answer or Kyle's answers is all you need to solve your question.

Source: INNER JOIN, LEFT/RIGHT OUTER JOIN

Community
  • 1
  • 1
Kevin Vasko
  • 1,561
  • 3
  • 22
  • 45
10

As far as a hint, you need to use a join of some sort (join fundamentals).

A possible answer is below:

Select p.ProductName, pu.Purchased, s.Sold
From Products p 
INNER JOIN Purchase pu on p.ProductID = pu.ProductID
INNER JOIN Sale s on s.ProductID = p.ProductID
Kyle Williamson
  • 2,251
  • 6
  • 43
  • 75
  • That's not nice!!! *Give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime*. **You just gave him a fish.** +1 though!!! – Satwik Nadkarny Mar 04 '16 at 20:57
  • Him and 12k other people. If you fished the answer in some other question, you could flag this one as duplicate, you know. – Klesun Feb 05 '20 at 16:08