January 5, 2018 at 9:25 am
Hello,
I'm fairly new to SQL and seem to be struggling with the concept of "one to many". I'll use the north wind DB for my example.
using employee table, I want to iterate through the orders table (one to many) and if there is an order that shipped to Mexico, I want to update my SHIPTOMEXICO field with Y otherwise N. I don't care how many times it shipped to mexico. I think I know whats happening to with my code, just don;t know how to correct. It appears my SHIPTOMEXICO column is getting updated with the last instance of orders being evaluated in the orders table. Basically, I think its "overriding" any Y value that's been populated previously.
I'd really appreciate some help understanding how to go about solving this issue. (I've attached a doc with a little more info if needed)
if object_id('tempbd..#temp') is not null begin drop table #temp end
select
employeeId
,e.FirstName
,e.LastName
,convert(varchar(255),null) as [SHIPTOMEXICO]
into #temp
from employees e
update #temp
set #temp.SHIPTOMEXICO = (case when orders.ShipCountry='mexico' then 'Y' else 'N' END)
from #temp
left outer join orders on #temp.EmployeeID=orders.EmployeeID
select *from #temp
January 5, 2018 at 9:53 am
Why not do it all in one go, something like this? The code may not be 100% correct since you didn't provide any consumable DDL or sample data to test it on, but I'm sure you get the idea.
if object_id('tempbd..#temp') is not null begin drop table #temp end
select
employeeId
,e.FirstName
,e.LastName
,MAX(CASE WHEN ShipCountry = 'Mexico' THEN 'Y' ELSE 'N' END) OVER (PARTITION BY employeeid, firstname, lastname) as [SHIPTOMEXICO]
into #temp
from employees e
John
January 5, 2018 at 9:58 am
John Mitchell-245523 - Friday, January 5, 2018 9:53 AMWhy not do it all in one go, something like this? The code may not be 100% correct since you didn't provide any consumable DDL or sample data to test it on, but I'm sure you get the idea.
if object_id('tempbd..#temp') is not null begin drop table #temp end
select
employeeId
,e.FirstName
,e.LastName
,MAX(CASE WHEN ShipCountry = 'Mexico' THEN 'Y' ELSE 'N' END) OVER (PARTITION BY employeeid, firstname, lastname) as [SHIPTOMEXICO]
into #temp
from employees eJohn
Because the ShipCountry isn't in the Employee table?
January 5, 2018 at 10:05 am
Lynn Pettis - Friday, January 5, 2018 9:58 AMJohn Mitchell-245523 - Friday, January 5, 2018 9:53 AMWhy not do it all in one go, something like this? The code may not be 100% correct since you didn't provide any consumable DDL or sample data to test it on, but I'm sure you get the idea.
if object_id('tempbd..#temp') is not null begin drop table #temp end
select
employeeId
,e.FirstName
,e.LastName
,MAX(CASE WHEN ShipCountry = 'Mexico' THEN 'Y' ELSE 'N' END) OVER (PARTITION BY employeeid, firstname, lastname) as [SHIPTOMEXICO]
into #temp
from employees eJohn
Because the ShipCountry isn't in the Employee table?
+100! Anyone still have a copy of Northwind?
...
January 5, 2018 at 10:26 am
I've attached a sample of the database, please open attachment.
January 5, 2018 at 10:35 am
To be helpful, you seem to want to update the orders table. I doubt there is a column ShippedToMexico, more likely ShippedTo, so that is the column you wish to update, consider what you have in your temp table as the convert statement makes no sense. Hint, use the join you used in your second query using the employee table.
...
January 5, 2018 at 10:52 am
Not quite
Table Employees contains 9 employees, table Orders contains multiple orders for each employee. If the employee had Any order that shipped to Mexico, I want to update a column I created called SHIPTOMEXICO with Y or N. I want to answer the question "for each employee, do they have any orders that shipped to mexico?" I created a temp table because I may need to take the base data (temp table columns) and use it to gather additional information from other tables as I continue.
January 5, 2018 at 11:45 am
There are a few ways to do this.
Here are 3:
SELECT
e.EmployeeID
,e.FirstName
,e.LastName
,ShipToMexico=CASE WHEN MAX(o.ShipCountry) IS NULL THEN 'N' ELSE 'Y' END
FROM
Employees e
LEFT JOIN
Orders o ON e.EmployeeID=o.EmployeeID
AND
o.ShipCountry='Mexico'
GROUP BY
e.EmployeeID
,e.FirstName
,e.LastName
;
SELECT
e.EmployeeID
,e.FirstName
,e.LastName
,ShipToMexico=MAX(CASE WHEN o.ShipCountry='Mexico' THEN 'Y' ELSE 'N' END)
FROM
Employees e
LEFT JOIN
Orders o ON e.EmployeeID=o.EmployeeID
GROUP BY
e.EmployeeID
,e.FirstName
,e.LastName
;
SELECT
e.EmployeeID
,e.FirstName
,e.LastName
,ShipToMexico=ISNULL(o.ShipToMexico,'N')
FROM
Employees e
LEFT JOIN
(
SELECT DISTINCT EmployeeID, ShipToMexico='Y'
FROM orders
WHERE ShipCountry='Mexico'
) o ON e.EmployeeID=o.EmployeeID
;
Cheers!
January 5, 2018 at 12:02 pm
THANKS SO MUCH!!! This is great. I do want to LEARN/UNDERSTAND versus just plugging in what you provided so...
I'm not fully understanding the BOLD line. Can you explain what and why your doing this?
SELECT
e.EmployeeID
,e.FirstName
,e.LastName
,ShipToMexico=CASE WHEN MAX(o.ShipCountry) IS NULL THEN 'N' ELSE 'Y' END
FROM
Employees e
LEFT JOIN
Orders o ON e.EmployeeID=o.EmployeeID
AND
o.ShipCountry='Mexico'
GROUP BY
e.EmployeeID
,e.FirstName
,e.LastName
;
January 5, 2018 at 12:12 pm
Sure!
Because we're doing a left join with the criteria that the employeeid match and that the shipcountry is Mexico, the rows coming out of the join will have either a ShipCountry of Mexico (this is the case when an employee had a row in orders with their employeeid and a shipcountry of Mexico, i.e., the join criteria were true), or a NULL ShipCountry (when the join criteria were not true, which would be the case when an employee has no orders with a ShipCountry of Mexico).
The MAX(o.ShipCountry) gives us the maximum value of the ShipCountry column for each combination of values of the grouping columns we've chosen (here that's for each EmployeeID, FirstName, and LastName). For employees that had a row in Orders with a ShipCountry of Mexico, the MAX will be Mexico.
For those who did not, they'll only have a single row with NULL for ShipCountry, so the MAX will return NULL.
The CASE expression then says "If the result of the MAX is NULL, then return N , and if the MAX is anything else (could only be Mexico), then return Y".
Hopefully that helps! If not just let me know and I'll try to articulate it more clearly.
Cheers!
January 5, 2018 at 12:36 pm
Another way, which may not matter for NorthWind, but could be slightly more efficient for a bigger database, is to only test for EXISTS as opposed to doing a JOIN:SELECT e.EmployeeID, e.FirstName, e.LastName,
ShipToMexico = CASE WHEN EXISTS (SELECT o.OrderID FROM dbo.Orders o WHERE o.EmployeeID = e.EmployeeID AND o.ShipCountry='Mexico' ) THEN 'Y' ELSE 'N' END
FROM dbo.Employees e
that way when SQL Server is evaluating the Orders table, it can essentially stop looking for more Mexico orders for an employee once it finds one for that employee.
January 8, 2018 at 2:09 am
HappyGeek - Friday, January 5, 2018 10:05 AMLynn Pettis - Friday, January 5, 2018 9:58 AMJohn Mitchell-245523 - Friday, January 5, 2018 9:53 AMWhy not do it all in one go, something like this? The code may not be 100% correct since you didn't provide any consumable DDL or sample data to test it on, but I'm sure you get the idea.
if object_id('tempbd..#temp') is not null begin drop table #temp end
select
employeeId
,e.FirstName
,e.LastName
,MAX(CASE WHEN ShipCountry = 'Mexico' THEN 'Y' ELSE 'N' END) OVER (PARTITION BY employeeid, firstname, lastname) as [SHIPTOMEXICO]
into #temp
from employees eJohn
Because the ShipCountry isn't in the Employee table?
+100!
Note that I said "something like this". If ShipCountry is in a different table, join to it. My point was, and still is, that you don't need to insert into a temp table and then do an update.
John
January 8, 2018 at 10:23 am
John Mitchell-245523 - Monday, January 8, 2018 2:09 AMHappyGeek - Friday, January 5, 2018 10:05 AMLynn Pettis - Friday, January 5, 2018 9:58 AMJohn Mitchell-245523 - Friday, January 5, 2018 9:53 AMWhy not do it all in one go, something like this? The code may not be 100% correct since you didn't provide any consumable DDL or sample data to test it on, but I'm sure you get the idea.
if object_id('tempbd..#temp') is not null begin drop table #temp end
select
employeeId
,e.FirstName
,e.LastName
,MAX(CASE WHEN ShipCountry = 'Mexico' THEN 'Y' ELSE 'N' END) OVER (PARTITION BY employeeid, firstname, lastname) as [SHIPTOMEXICO]
into #temp
from employees eJohn
Because the ShipCountry isn't in the Employee table?
+100!
Note that I said "something like this". If ShipCountry is in a different table, join to it. My point was, and still is, that you don't need to insert into a temp table and then do an update.
John
He did join to the other table and isn't getting the results he was expecting.
January 9, 2018 at 1:04 am
Lynn Pettis - Monday, January 8, 2018 10:23 AMJohn Mitchell-245523 - Monday, January 8, 2018 2:09 AMNote that I said "something like this". If ShipCountry is in a different table, join to it. My point was, and still is, that you don't need to insert into a temp table and then do an update.John
He did join to the other table and isn't getting the results he was expecting.
Only as part of the update. Note that I suggested doing everything in the insert operation.
John
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply