SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Many to Many Relationships – Day 8 of 35

Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 2.
Every day one winner from United States will get Joes 2 Pros Volume 2.

Many to Many relationships

If anyone has done some shopping on the internet you are familiar with the term “Shopping Cart” or “Shopping basket”. After you have selected a product you want to buy the storefront will gladly let you keep on shopping until there are many items in you shopping cart. On my last trip to Amazon.com I put 3 things in my cart before checking out. One item was a HDMI cable box converter for my flat screen TV at home. I took a closer look at this products rating and reviews before buying. Below this product it said this items was in the top 100 selling items in home electronics. This means this items has sold to more people than just me and I was not the first to buy this product.

Based on the story above what is the relationship to a product (like the HDMI cable box) and a customer who may want to buy that item? As a customer I can buy many products at once in my shopping cart. Does this mean there is a one to many relationship between customers (like me) and products (like the HDMI cable box). Yes there is but that is only a half truth. A product like the HDMI cable box can be purchased by many different customers. Therefore there is a Many to Many relationship between Customers and products. This product can be bought by many customers and many customer can buy this product.

Another many to many relationship is between books and Authors. I have written 5 books so we know there is at least a one to many relationship between Authors and Books. Now look at my 4th book (SQL Programming Joes 2 Pros Volume 4 – ISBN-13: 978-1451579482). This book has two authors (Rick A. Morelan and Pinal Dave). Since an Author can write many books and a book can be written by many authors there is a many to many relationship between authors and books.

We need a new process to map this complex relationship. Sometimes tables exist for the sole purpose of allowing indirect relationships between tables. What do you call tables the handle relationships for other tables? Common terms for these type of tables are mapping tables, bridge tables, or junction tables.

Many to Many relationships with invoicing systems

As discussed earlier a product can appear on many invoices and an invoice can have many products. The relationship between products and invoices is known as a many-to-many relationship as seen in the figure below.

If you were to ask yourself “What Products are on Invoice 5631?” or “Go-Duck was ordered on how many invoices?” you could resolve this query? The Figure below shows us how to resolve both mappings. We can see the Go-Duck toy was order twice (on 7/15 and on 9/22). We can also see the shopping cart 5631 has two items in it (Toy Car and Furchee).

Now we’ll take a look at many-to-many relationships between sales invoices and products in the JProCo sample database.

Here is an example of JProCo’s sales invoices mapping to a bridge table (SalesInvoiceDetail) in order to map over to the CurrentProducts table. The CurrentProducts table gives us all the detail of the current products that have been ordered.

With SalesInvoice 5 It looks like many products were ordered on that one invoice (Products 9, 11, 12, and 16). To see what those products are, we would look over to the CurrentProducts table. We see Product 9 is an Underwater Tour 3 Days East Coast. Product 11 is an Underwater Tour 1 Week East Coast, and so forth. So a SalesInvoice can have many products, and products can be ordered on multiple sales invoices.

The SalesInvoiceDetail table is the mapping table that is handling the many to many relationship between the SalesInvoice table and the CurrentProducts table. This 3 table join between these tables would look like the query you see below.

SELECT si.CustomerID, si.InvoiceID, si.OrderDate,
sd.Quantity, cp.ProductName, cp.RetailPrice
FROM dbo.SalesInvoiceDetail AS sd
INNER JOIN dbo.SalesInvoice AS si
ON sd.InvoiceID = si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLQueriesChapter3.0Setup.sql script from Volume 2.

Question 8

Q.8) You have tables named dbo.SalesInvoice and dbo.SalesInvoiceDetail. CustomerID is located in the SalesInvoice table and InvoiceID is located in both tables. You have been told to show the discount amounts from the SalesInvoiceDetail table that correspond to the sales of a specific CustomerID of 490. Which T-SQL statement should you use?

  1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490
  2. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490
  3. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    WHERE EXISTS (dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490)

Please post your answer in comment section to win Joes 2 Pros books.

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 2.
Winner from India will get Joes 2 Pros Volume 2.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

127 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Many to Many Relationships – Day 8 of 35

  1. Answer No. 1 is the right one. It’s a little bit tricky question because CustomerID is located in the SalesInvoice table (alias si), but you can see that all the answers go for SalesInvoiceDetail table (alias sd) to get CustomerID field, so CustomerID field might be located in SalesInvoiceDetail table too…

    Rene Castro
    El Salvador

  2. Correct answer is option 1:

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    because option 1 has relation between SalesInvoice and SalesInvoiceDetail
    also between CurrentProducts and SalesInvoiceDetail tables

    Second option does n’t have relation between SalesInvoice and SalesInvoiceDetail

    Third option is not a valid query

    Thanks.

    Country – India

  3. option1 is the query we will use.
    bcz salesinvoice and invoicedetails have a relation based on invoiceid.
    and we tie these 2 tables with the invoiceid

    Good posts, short and to the point.

    Boston USA

  4. 1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    I think this T-SQL statement will give expected result.

    India

    -Dnyanesh

  5. Option 1.
    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

  6. Option 1
    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Leo Pius
    USA

  7. Option 1 is right answer.

    1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Neelesh
    India

  8. Answer:1

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Kulwant kumar
    Delhi India

  9. the answer is

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    country :India [Ahmedabad]

  10. Correct Answer is # 1.

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Gopalakrishnan Arthanarisamy
    Bangalore, India

  11. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  12. Option 1.
    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    will give a correct result.

  13. Correct Answer is option 1.

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    From,
    Malay Shah
    City:Ahmedabad
    Country: India

  14. Correct Answer: Query #1
    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Country of residence: India

  15. Answer: Query #1

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Country: India

  16. The correct answer is option 1 that is

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Here we know that SalesInvoiceDetail and SalesInvoice table has “InvoiceID” column collon in both tables so that we can inner join on that. Also we know that we have “ProductID” column in both CurrentProducts and SalesInvoiceDetail table so that we can make Inner Join on that. Also we only details of CustomerID= 490
    , so that we have to put this into “Where” clause.

    I am from
    INDIA
    AHMEDABAD

  17. Hi Pinal,
    I believe that the alias for one of the tables is not correct, but if I would go for it it would be

    1. SELECT si.CustomerID, sd.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Location US – Minneapolis.

  18. Hi,
    Q.8) You have tables named dbo.SalesInvoice and dbo.SalesInvoiceDetail. CustomerID is located in the SalesInvoice table and InvoiceID is located in both tables. You have been told to show the discount amounts from the SalesInvoiceDetail table that correspond to the sales of a specific CustomerID of 490. Which T-SQL statement should you use?

    Ans 1
    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    I am from India

  19. The Correct Answer is Option 1:

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    India.

  20. Correct answer is option 1:

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Pratik Raval
    India

  21. Option 1. is the Answer
    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    (Sale, Nigeria)

  22. Option 1 is correct answer.

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Varinder Sandhu (India)

  23. option 1 should do the trick, so answer should be:

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Ritesh Shah (India)

  24. answer is option 1:

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Thanks
    Riyas.V.K

  25. Option 1 is the answer:

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    INDIA
    Brijesh

  26. option 1 should do the trick, so answer should be:

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    – Rahul Sharma Noida India

  27. Choice 1 is the Answer

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Country: India

  28. In my opinion all the 3 options are incorrect, as it says “sd.CustomerID” which means it is to be fetched from SalesInvoiceDetail however SD does not contain CustomerID. CustomerID belongs to SalesInvoice table.

    If its a spelling mistake, then its altogether a different scenario.

    Krunal K. Doshi
    INDIA

  29. Correct answer is option 1:

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Cheers,
    Prasad Yangamuni
    INDIA (Pune)

  30. Correct Option is 1
    i.e, SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    I’m from INDIA

  31. Option 1 is correct

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Thanks
    Vivek Srivastava

  32. Missed the country. Reposting the comment, please ignore previous one.

    Option 1 is correct

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Thanks
    Vivek Srivastava

    Country INDIA

  33. correct answer is : 1

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

  34. Question 8

    Q.8) You have tables named dbo.SalesInvoice and dbo.SalesInvoiceDetail. CustomerID is located in the SalesInvoice table and InvoiceID is located in both tables. You have been told to show the discount amounts from the SalesInvoiceDetail table that correspond to the sales of a specific CustomerID of 490. Which T-SQL statement should you use?

    Answer is 1.
    1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

  35. Option 1 is the correct one.

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    country: India

    Thanks
    GurjitSingh

  36. Question 8

    Q.8) You have tables named dbo.SalesInvoice and dbo.SalesInvoiceDetail. CustomerID is located in the SalesInvoice table and InvoiceID is located in both tables. You have been told to show the discount amounts from the SalesInvoiceDetail table that correspond to the sales of a specific CustomerID of 490. Which T-SQL statement should you use?

    correct answer is 1:

    1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

  37. Correct Answer: 1
    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    India

  38. Option 1 is the correct answer.

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Country -India

  39. Option No. 1
    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Somnath Desai

    India

  40. Answer is no:1

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Thanks & Regards
    Karthikeyan.T
    INDIA

  41. The correct answer is option 1:
    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Jankhana,
    India.

  42. Ans:
    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Partha
    India

  43. Option 1

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Annie
    Bangalore
    -India

  44. Answer
    Option 1
    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Country:India

  45. Hi Pinal,
    Table alians are not correct in any option.
    Correct answer should be

    SELECT si.CustomerID, sd.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

  46. Hi Pinal,
    Table alians are not correct in any option.
    Correct answer should be

    SELECT si.CustomerID, sd.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    I am from India

  47. option (1) is correct answer
    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    from india

  48. Hi Pinal,
    I simply say 3 options are wrong. First option query almost correct but it’s column alias interchanged. I have corrected query following..

    SELECT si.CustomerID, sd.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail As sd
    INNER JOIN dbo.SalesInvoice As si ON sd.InvoiceID = si.InvoiceID
    INNER JOIN CurrentProducts As cp ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

  49. Answer is : SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    SalesInvoice has InvoiceID
    SalesInvoiceDetail has InvoiceID

    and both are connected
    but CurrentProducts has ProductID which connect with SalesInvoiceDetail.ProductID

    Country : India

  50. Hi Pinal,
    I simply say 3 options are wrong. First option query almost correct but it’s column alias interchanged. I have corrected query following..

    SELECT si.CustomerID, sd.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail As sd
    INNER JOIN dbo.SalesInvoice As si ON sd.InvoiceID = si.InvoiceID
    INNER JOIN CurrentProducts As cp ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Country : India

  51. Correct Option is 1.

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    karan
    India

  52. Correct option is option 1

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    as SalesInvoiceDetail makes an inner join with SalesInvoice on InvoiceID and further for every invoice we have productid, so SalesInvoiceDetail makes an inner join with CurrentProducts for CustomerID=490

    Sumit
    India

  53. Correct Answer : 1

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Shekhar Gurav.
    Country – INDIA

  54. Correct Option: 1

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    By this query, system will identify invoices For customer: “WHERE si.CustomerID= 490″ and then Find out Invoice details by “dbo.SalesInvoiceDetail sd INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID”

    Country: INDIA

    Tejas

  55. Correct option is number 1:

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    INDIA

  56. Answer: Option 1

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Country: India(sikha)

  57. None of them acutally run on the sample database. I assume there is just a typo in #1 and that the answer should be (note I changed the alias/column names to match your screen shot to get this to work for CustomerID and sd.DiscountAmt)

    1)SELECT si.CustomerID, sd.UnitDiscount, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    I’m picking 1 assuming typos because #2 references columns that do not exist in either table and #3 exists clause is incorrectly written

    from the USA

  58. Option 1 is the Correct answer

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    It has relation between SalesInvoice and SalesInvoiceDetail and also between CurrentProducts and SalesInvoiceDetail tables

    Yasodha.N(India)

  59. Hi Pinal,
    I think given queries are wrong because SalesInvoiceDetail Table Not Having CustomerID column. please check it.

  60. Hi Pinal,
    I think given queries are wrong because SalesInvoiceDetail Table not having CustomerID column. please check it.
    Otherwise if alias interchanged then option 1 is correct.

    Pune,India.

  61. 1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Texas, USA

  62. Here is the answer:

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Texas, USA

  63. None of the answers are correct, however the “most” correct option is 1:

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Explanation: To filter on customerID we need to join on salesinvoice si. The question tells us to get the discount amounts from the salesinvoicedetail sd, but the discount amount being retreived in option 1’s select statement comes from salesinvoice si. The question also tells us that the customerID field is in the salesinvoice table si. We’re filtering on the correct customerID field, but I think the table aliases were transposed in the SELECT portion of the statement and shoudl be si.CustomerID and sd.DiscountAmt.

    These could just be a typos. Other than that, the joins are correct so that we could get the information we need.

    Option2 doesn’t join against salesinvoice which contains the customer ID

    Option3 is not a valid statement

    Country of Residence: USA

  64. Option 1 is Right

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Yeou Sunn

    India

  65. Option 1 is correct in logic but need to fix typo: CustomerID is in si and DiscountAmt is in sd:

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    USA

  66. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Chetan – USA

  67. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490
    kkmjssate
    india

  68. Hi Pinal,

    Challenge:
    Q.8) You have tables named dbo.SalesInvoice and dbo.SalesInvoiceDetail. CustomerID is located in the SalesInvoice table and InvoiceID is located in both tables. You have been told to show the discount amounts from the SalesInvoiceDetail table that correspond to the sales of a specific CustomerID of 490. Which T-SQL statement should you use?

    Correct Answer:
    The first choice is the correct answer:
    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Explanation:
    The first query is correct. The second query doesn’t even reference the SalesInvoice table, so that query is discarded. The third query uses a WHERE EXISTS incorrectly and won’t compile.

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

  69. This is a very interesting and tricky question, as none of the answers is correct. I spent about 5 or more minutes looking at these 3 queries before coming to this conclusion.

    Answer #3 can be eliminated right away as the syntax is wrong.

    Answer #2 can be eliminated also as it doesn’t reference SalesInvoice table at all

    Finally, answer #1 is almost correct, but it references sd.CustomerID, which is wrong as CustomerID belongs to SalesInvoice table. Therefore none of the answers is correct.

    So, my answer will be:

    SELECT si.CustomerID, sd.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp — if we need to show Product Names we join with CurrentProducts table
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    It’s the same as #1 with the corrected typo.

    I am from USA

  70. I think the alias is wrong in the first option’s query but if I have to go with given options then I assume that “CustomerID” is also present in dbo.SalesInvoiceDetail table.

    So, the correct answer is option (1).

    Thank You.

    Country – India.

  71. Hi,
    the Answer is 1.

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    We can inner join the tables to get the required fields and filter it by the alias name of table.

    SivaSakthiVel

    – INDIA

    “Save Our tigers”

  72. #1 is the correct answer–SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Rahul Thapa–India

  73. Option 1 will return an invalid column
    Option 2 does not join to a table that contains the CustomerId
    Option 3 is not valid.

    Option 1 is the closest in that it has the correct joins, but needs to have the select statement edited as follows:
    SELECT si.CustomerID, sd.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Thanks for the fun question. I’m from the USA.
    Mike

  74. Hi Sir,

    As per the question you have said that “CustomerID is located in the SalesInvoice table”

    but in all the queries the select statement is like
    “SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd”

    so as per this all the queries will fail.

    If that was a mistake done while typing then answer 1 is the correct option.

    i.e “SELECT si.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490″

    Anish Shenoy
    INDIA,Bangalore,Karnataka

  75. Hi Sir
    I’ll use option number one to run this t-sql

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Leonardo Guerrero
    from Chile

  76. answer:
    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    lalit
    india

  77. 1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Gordon Kane
    Allen TX
    USA

  78. Answer 1 is correct:

    “SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490″

    Rajneesh Verma
    (INDIA)

  79. Answer

    1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Thank You
    Ramdas

  80. Hello,

    Find my answer below :

    2nd and 3rd option is wrong. if we consider 1st option then it will give an error message like “Invalid column name si.DiscountAmt” .

    If we rectify the 1st option query by modifying si.DiscountAmt to sd.DiscountAmt. then it will give you error free output.

    Hope you understand…

    I am from India..

  81. Correct Answer for the above question is Option : 1.

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    DILIP KUMAR JENA

    Country : INDIA

  82. As you said : “CustomerID is located in the SalesInvoice table and InvoiceID is located in both tables. You have been told to show the discount amounts from the SalesInvoiceDetail table that correspond to the sales of a specific CustomerID of 490. Which T-SQL statement should you use? ”

    So Query should be :

    SELECT si.CustomerID, sd.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

  83. Correct answer is :

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Country: United States

  84. The (most) correct option is 1:

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Option2 -> doesn’t join against salesinvoice which contains the customer ID

    Option3 -> is not a valid statement

    Country of Residence: USA

  85. Option 1

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    USA

  86. 1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

  87. 1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Divesh
    INDIA

  88. Correct Answer is Option : 1.

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Bablu

    Country : – INDIA

  89. Answer 1 is the correct one

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

  90. correct answer, is option 1 modified,
    SELECT si.CustomerID,sd.UnitDiscount,cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490
    C. Lennick, Country: USA

  91. Correct answer is option 1:

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Shilpa

    India

  92. Option 1

    1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Thanks

    Shree

  93. Question 8
    Right answer Opt : #1

    1- SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Chennai ,INDIA

  94. Option 1 is the right Answer,I.e.

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Because Here we want Discount from SalesInvoiceDetail Table and For Customer No 490. CustomerNo exist in SalesInvoice Table and Discount is available in SalesInvoiceDetail Table and their must be a relation between SalesInvoice and SalesInvoiceDetail Table to get the exact value and that we are performing in above given query.

    Alok Chandra Shahi
    India

  95. Option 1 is correct. Because, it considers both SalesInvoice table holding CustomerID. Other tables dont have CustomerID column. Option 2 and Option 3 queries will fail, as they dont have the Select clauses aliases in the FROM Clause. So, the correct answer is Option 1, which will successfully get the results.

    1) 1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Venkataraman R,
    USA.

  96. Q 8) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Many to Many Relationships – Day 8 of 35

    A.) The code in (2) does not look at the SalesInvoice Table which contains the CustomerID field so it will return an error. The use of EXISTS with a subquery in the WHERE clause will either return every row in the SalesInvoiceDetail table or none of them, regardless of the CustomerID, so (3) is also incorrect. Because ‘dbo. SalesInvoiceDetail sd INNER JOIN dbo.SalesInvoice si ON sd.InvoiceID = si.InvoiceID’ will return only records that have a match from both tables and ‘WHERE si.CustomerID = 490’ will filter the results to only display records having a CustomerID of 490, (a) is the ALMOST correct answer.
    However, the SELECT of the query references sd.CustomerID, which is wrong as CustomerID belongs to SalesInvoice table.

    The correct answer is 
    SELECT si.CustomerID, sd.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp — if we need to show Product Names we join with CurrentProducts table
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490
    

    This one was indeed Tricky one to test how many really reads the whole blog post :)

    Winner from USA: David Seefeld

    Winner from India: sandipak

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

  97. Option 1 is the right Answer,I.e.

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Because Here we want Discount from SalesInvoiceDetail Table and For Customer No 490. CustomerNo exist in SalesInvoice Table and Discount is available in SalesInvoiceDetail Table and their must be a relation between SalesInvoice and SalesInvoiceDetail Table to get the exact value and that we are performing in above given query.

    Aditi
    India

  98. Answer is #1:

    SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

    Thanks,
    Wayne

  99. Option 1 Is the only query that would compile, but the correct answer is none of the above. Either:
    SELECT DiscountAmt
    FROM dbo.SalesInvoice
    WHERE CustomerID= 490
    OR
    SELECT SUM(DiscountAmt) [Total Discount]
    FROM dbo.SalesInvoice
    WHERE CustomerID= 490
    Depending on whether the question was a list of the individual discounts the customer got with each order or if they wanted the total discount. The problem with the first one is that it gives detailed information with no context. (When did the customer make each order, etc.) The second might benifit from more context as well:
    SELECT SUM(DiscountAmt) [Total Discount], COUNT(InvoiceID) [Total Orders]…

    Interesting, I made the assumption that DiscountAmt was an aggregate amount stored in the invoice table. It isn’t in your table diagram. (Made sense, discount amount could be affected quantity ordered, etc. and I don’t mind some denormalization…)
    Making some more assumptions about what is wanted and using just diagrammed table schemas, I come up with:
    SELECT cp.ProductName, COUNT(DISTINCT si.InvoiceID) [Total Orders]
    , SUM(sd.Quantity*sd.UnitDiscount) [Total Discount]
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID AND si.CustomerID=490
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    GROUP BY cp.ProductName

    If you want, throw in “si.CustomerID, ” (NOT sd. – I take it back, NO option given would compile with the given schema) to both the SELECT and GROUP, but I’d only do it if more than one customer would be listed. (Would be an interesting schema if the customer was in sd, because that means a single invoice could be set up for multiple customers.) I put in DISTINCT because a unique index on “invoice, product” ids wasn’t declared in the detail table. (Logical schema setup and then distinct is only needed if you want a one line result for all products)
    Sorry, I wasn’t consise, didn’t read all the way down to see if these errors were spotted or not.

  100. Pingback: SQL SERVER – SQL Query Techniques For Microsoft SQL Server 2008 – Book Available for SQL Server Certification Journey to SQLAuthority

  101. Pingback: SQL SERVER – Identity Fields – Contest Win Joes 2 Pros Combo (USD 198) – Day 2 of 5 « SQL Server Journey with SQL Authority

  102. 1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490

  103. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s