Part 3- Joins Continued - SQL Select (Part 3) Multi-Table...

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

View Full Document Right Arrow Icon
SQL Select (Part 3) Multi-Table Joins
Background image of page 1

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

View Full DocumentRight Arrow Icon
Multi-Table Select Join – combining columns (fields) from multiple tables Tables can be real or virtual Technically, a table can be joined to itself multiple times. It is known as a self-join.
Background image of page 2
Select Syntax SELECT FROM WHERE GROUP BY HAVING ORDER BY fields / functions tables row conditions fields aggregation conditions fields
Background image of page 3

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

View Full DocumentRight Arrow Icon
Naming Convention Server . Database . Owner . Table . Field mis325 . dbo . tblStudent . FirstName tblEmployee . Phone tblCustomer . Phone UTadmin . Personnel . AW8137 . tblEmployee . Gender
Background image of page 4
Implicit Inner Join SELECT FROM WHERE AND ORDER BY fields tblX, tblY tblX.fieldX = tblY.fieldY other row conditions fields tblX tblY
Background image of page 5

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

View Full DocumentRight Arrow Icon
Inner Join Bad Accounting Old Employees New Employees Employees Paychecks
Background image of page 6
Referential Integrity Old Employees New Employees Employees Paychecks
Background image of page 7

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

View Full DocumentRight Arrow Icon
Database Model 10,000 Employees 500,000 Paychecks
Background image of page 8
Example Show the employee concatenated first and last names, the paycheck date and the net amount for all paychecks. Sort the rows by ascending last and first name and by descending date (i.e., most recent first)
Background image of page 9

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

View Full DocumentRight Arrow Icon
Paid Employees and their Paychecks SELECT tblEmployee. FirstName + ' ' + tblEmployee. LastName AS Employee, tblPaycheck. PayDate, tblPaycheck. NetAmount FROM tblEmployee, tblPaycheck WHERE tblEmployee.EID = tblPaycheck.EID ORDER BY tblEmployee. LastName, tblEmployee. FirstName, tblPaycheck. PayDate DESC Note: Table name is only necessary when field name is not unique within the query
Background image of page 10
Paid Employees and their Paychecks SELECT FirstName + ' ' + LastName AS Employee, PayDate, NetAmount FROM tblEmployee, tblPaycheck WHERE tblEmployee.EID = tblPaycheck.EID ORDER BY LastName, FirstName, PayDate DESC Note: Table name is only necessary when field name is not unique within the query
Background image of page 11

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

View Full DocumentRight Arrow Icon
Explicit Inner Join SELECT FROM ON WHERE ORDER BY fields tblX INNER JOIN tblY tblX.fieldX = tblY.fieldY row conditions fields Note: INNER JOIN can be abbreviated as JOIN in SQL Server
Background image of page 12
Image of page 13
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/17/2010 for the course MIS 03955 taught by Professor Mote during the Fall '10 term at University of Texas at Austin.

Page1 / 42

Part 3- Joins Continued - SQL Select (Part 3) Multi-Table...

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

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