More on SQL Joins Week 4

More on SQL Joins Week 4 - More on SQL Joins, Introduction...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
More on SQL Joins, Introduction to Reporting Services Multi-Table Joins | SQL Server Reporting Services (SSRS) | Creating Shared Data Sources | Understanding Report Layouts | The Table Control | Adding a Grouping Level | Previewing Reports | Calculated Fields | Page Numbers | Tutorials We can now extract data from our tables, but we are limited in the way that data can be presented to the users. The output from a SELECT statement is not very exciting. Users want reports that are easy to read and therefore easy to understand and interpret. Presenting information to the users is a two step process – extracting the required data from the database and then creating a well-formatted, professional-looking report. Extracting the data is achieved with SELECT statements. To create a report, we will need to use report-writing software. Before creating reports, we will look at some more complex joins where we need to extract data from more than two tables. In the previous few weeks, we have looked at using functions in our SELECT statements combined with WHERE, ORDER BY, and HAVING clauses. We can continue to make use of these features when joining tables. Multi-Table Joins Last week, we started joining tables in order to respond to more complex business needs. We produced a list of consumer names and their order information: SELECT C.ConsumerID, C.LastName, C.FirstName, O.OrderID, O.OrderDate, O.CustomerPO FROM Consumers C INNER JOIN Orders O ON C.ConsumerID = O.ConsumerID; While this list may be useful, it doesn’t give any information about what products were ordered or the quantity ordered. The details regarding the items ordered can be found in the OrderLines table. To include this information, we need to join the OrderLines table in our query. How do we join a third table? If you look at the ER diagram for Coffee Merchant, you will see that the Consumers table is related to the Orders table on the ConsumerID field, and the Orders table is related to the OrderLines table using the OrderID field. To change our statement, we need to add the extra fields from the OrderLines table and join OrderLines in the FROM clause: SELECT C.ConsumerID, C.LastName, O.OrderID, O.OrderDate, O.CustomerPO, OL.LineItem, OL.InventoryID, OL.Quantity FROM Consumers C INNER JOIN Orders O ON C.ConsumerID = O.ConsumerID INNER JOIN OrderLines OL ON O.OrderID = OL.OrderID; We can also include the ORDER BY clause to sort the result. Let’s sort the rows into ConsumerID order: SELECT C.ConsumerID, C.LastName, O.OrderID, O.OrderDate, O.CustomerPO, OL.LineItem, OL.InventoryID, OL.Quantity FROM Consumers C INNER JOIN Orders O ON C.ConsumerID = O.ConsumerID INNER JOIN OrderLines OL ON O.OrderID = OL.OrderID ORDER BY C.ConsumerID;
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
If we only want to see these details for the inventory item with an ID of 104, then we need to add a WHERE clause and restrict the output: SELECT C.ConsumerID, C.LastName, O.OrderID, O.OrderDate, O.CustomerPO, OL.LineItem, OL.InventoryID, OL.Quantity
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 11/27/2009 for the course BIS 345 taught by Professor Jones during the Summer '09 term at DeVry Cincinnati.

Page1 / 6

More on SQL Joins Week 4 - More on SQL Joins, Introduction...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online