SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

anhamade
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,
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,Shippers

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.

khtan
In (Som, Ni, Yak)

Singapore
17608 Posts

Posted - 03/01/2014 :  00:16:56  Show Profile  Reply with Quote
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,
Suppliers,Customers,Shippers


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 http://northwinddatabase.codeplex.com/


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/01/2014 :  10:02:11  Show Profile  Reply with Quote
this is a good read on SQL JOINS
http://www.w3schools.com/sql/sql_join.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000