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-03-01 : 23:35:35
|
i have to change this problem to subquery. this is what have so far and my question is how do i include the customers subquery.what i have so farUSE northwindSELECT max(OrderID) as OrderID FROM Orderswhere EmployeeID in(select EmployeeIDfrom Employeeswhere LastName = 'Peacock' ) Question problemUSE northwindSELECT max(OrderID) as OrderID FROM Orders join Employees on Orders.EmployeeID = Employees.EmployeeID join Customers on Orders.CustomerID = Customers.CustomerID WHERE Employees.LastName = 'Peacock' and Customers.Phone = '(5) 555-3932' |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-03-02 : 00:56:07
|
[code]SELECT max(OrderID) as OrderID -- change this line to sub queryFROM Orders Employeeswhere EmployeeID in(select EmployeeIDfrom Employeeswhere LastName = 'Peacock' [/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
anhamade
Starting Member
7 Posts |
Posted - 2014-03-02 : 01:31:25
|
but i have to keep from as orders. this is the full question.Query #3: Please run the following query: (you should get “10535”) USE Cis11102_Northwind SELECT max(OrderID) as OrderID FROM Orders join Employees on Orders.EmployeeID = Employees.EmployeeID join Customers on Orders.CustomerID = Customers.CustomerID WHERE Employees.LastName = 'Peacock' and Customers.Phone = '(5) 555-3932' Using ONLY the “Orders” table in the “FROM” clause, create a new query, using sub queries, that will return the same result. quote: Originally posted by khtan
SELECT max(OrderID) as OrderID -- change this line to sub queryFROM Orders Employeeswhere EmployeeID in(select EmployeeIDfrom Employeeswhere LastName = 'Peacock' KH[spoiler]Time is always against us[/spoiler]
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-03-02 : 03:05:32
|
oh i see. That your original query should be finejust add another condition to the WHERE for the Customer sub queryWHERE EmployeeID in ( ... )AND CustomerID in ( ... ) KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-03-02 : 09:18:32
|
this is another way using subqueryUSE [Cis11102_Northwind]GO SELECT max(OrderID) as OrderID FROM Orders join Employees on Orders.EmployeeID = Employees.EmployeeID WHERE EXISTS (SELECT 1 FROM Customers WHERE Orders.CustomerID = CustomerID AND Phone = '(5) 555-3932' )AND Employees.LastName = 'Peacock' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|