Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anhamade
Starting Member
7 Posts |
 Posted - 2014-02-28 : 23:52:03
|
| so am doing this problem for class and i need your help guys.Problem -Query #2: We are going to create a shipping report for the boss. This query will involve just a few tables. Do NOT use a CROSS JOIN. Display the following: Employees table - Employee’s first name, last name Territories Table - Territory description Order Details table - unit price, quantity ordered Products table - product name Region table - region description Suppliers table – Company Name (label this as “Supplying Company”) Customers table – Company Name (label this as “Purchasing Company”) Shippers Table – Company name (label this as “Shipping Company”) Filter the output by: Order quantities at or greater than 50, territory of Boston or Cambridge, within the Eastern region, unit price of 8 dollars or more, the product name of “Jack’s New England Clam Chowder” (note: you must search for the complete product name, not just a portion of the name) Please paste both rows from the results tab, with all properly named headings. The query that i got but it keeps running.USE northwindselect Employees.FirstName,Employees.LastName,Territories.TerritoryDescription,[Order Details].UnitPrice,[Order Details].Quantity,Products.ProductName,Region.RegionDescription,Suppliers.CompanyName as 'Supplying Company',Customers.CompanyName as 'Purchasing Company',Shippers.CompanyName as 'Shipping Company'from Employees, Territories, [Order Details],Products,Region,Suppliers,Customers,Shipperswhere ([Order Details].Quantity >= 50) and (Territories.TerritoryDescription like '%Boston%' or Territories.TerritoryDescription like '%Cambridge%') and Region.RegionDescription like '%Eastern%' and [Order Details].UnitPrice >= 8.00 and Products.ProductName like 'Jack''s New England Clam Chowder'i have no idea what does he mean by i can't use cross joins and what kind of joins should i use then. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-03-01 : 00:16:56
|
quote:
i have no idea what does he mean by i can't use cross joins and what kind of joins should i use then.
Other than cross join, you have INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN etcbasically your query here is doing a CROSS JOIN, you did not specify the relationship between the tables in the WHERE clausefrom Employees, Territories, [Order Details],Products,Region,Suppliers,Customers,Shippers it will be better to use a INNER JOIN like belowFROM [Orders] INNER JOIN [Order Details] ON [Orders].OrderID = [Order Details].OrderID INNER JOIN [Customers] ON [Orders].CustomerID = [Customers].CustomerID INNER JOIN [Employees] ON [Orders].EmmployeeID = [Employees].EmployeeID the columns specify in the ON are basically the relationship between the two tablesyou can refer to the Northwind Schematic here http://northwinddatabase.codeplex.com/
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
sql problem