Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql problem
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

7 Posts

Posted - 02/28/2014 :  23:52:03  Show Profile  Reply with Quote
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 northwind
select Employees.FirstName,Employees.LastName,
[Order Details].UnitPrice,[Order Details].Quantity,
Suppliers.CompanyName as 'Supplying Company',
Customers.CompanyName as 'Purchasing Company',
Shippers.CompanyName as 'Shipping Company'

from Employees, Territories, [Order Details],Products,Region,

where ([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.

In (Som, Ni, Yak)

17689 Posts

Posted - 03/01/2014 :  00:16:56  Show Profile  Reply with 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 etc

basically your query here is doing a CROSS JOIN, you did not specify the relationship between the tables in the WHERE clause

from Employees, Territories, [Order Details],Products,Region,

it will be better to use a INNER JOIN like below

FROM 	[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 tables

you can refer to the Northwind Schematic here

Time is always against us

Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 03/01/2014 :  10:02:11  Show Profile  Reply with Quote
this is a good read on SQL JOINS

SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000