January 26, 2012 at 2:13 pm
tbl_Managers
Manager_ID Manager_Namecontract end
B8CF76A6-B8D6-44E0-A901-23B9BEFC8162Jose Marinho30/06/2010
5C2CA598-6C64-4682-8B37-6779665E2575David Moyes30/06/2009
78DD90BC-3A26-40B8-AFE3-81D0A865D252Alex Ferguson30/06/2007
0C748468-F77A-41F8-B27A-85B670011AE9Alan Curbishley31/07/2008
61529833-AFEC-458D-B3AE-D00EF8B9DCF3Arsene Wenger31/07/2008
tbl_Teams
Team_Id Manager ID team
778E3D9E-99CF-4ECA-BD6A-16AE8ED64CCD78DD90BC-3A26-40B8-AFE3-81D0A865D252Man Utd
5CF9D85A-1A6F-4A80-BAC1-1B268F85369461529833-AFEC-458D-B3AE-D00EF8B9DCF3Arsenal
C8EAB9F5-671D-4729-A517-24BEC7232FA5B8CF76A6-B8D6-44E0-A901-23B9BEFC8162Chelsea
70172D21-C41F-4D66-81D3-2A19FF59D45BNULL West Ham
4B1B100D-26FB-4B40-B3EB-E4DFF4BAF26D5C2CA598-6C64-4682-8B37-6779665E2575Everton
Using the above table, if i wanted to find out the teams without a manger, would i have to use a JOIN or a UNION. , i notice the NULL value, and am assuming thats what i need to get listed?? have some test questions im racking my brains to answer!!!
thankyou
Andy
January 26, 2012 at 2:17 pm
You would neither join nor union. Just look for rows in the teams table that don't have a value for Manager_ID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2012 at 2:28 pm
Thanks for the quick reply
so would something like this work?
SELECT Manager_ID FROM tbl_Teams WHERE value='NULL';
January 26, 2012 at 2:30 pm
or even
SELECT Team_Name FROM tbl_teams WHERE Manager_ID = 'NULL';
January 26, 2012 at 2:41 pm
andrewmarsh0 (1/26/2012)
or evenSELECT Team_Name FROM tbl_teams WHERE Manager_ID = 'NULL';
It should be ..
SELECT Team_Name FROM tbl_teams WHERE Manager_ID IS NULL
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 26, 2012 at 2:47 pm
great thanks - i had one more question if thats ok
if i had to select the managers without a team ??
i can see this manager_id is not under the team_id would that require a join?
0C748468-F77A-41F8-B27A-85B670011AE9 Alan Curbishley
January 26, 2012 at 2:54 pm
For that best bet would be to use NOT IN or NOT EXISTS. Books Online (the SQL help) will have details on syntax.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2012 at 2:59 pm
Bru Medishetty (1/26/2012)
andrewmarsh0 (1/26/2012)
or evenSELECT Team_Name FROM tbl_teams WHERE Manager_ID = 'NULL';
It should be ..
SELECT Team_Name FROM tbl_teams WHERE Manager_ID IS NULL
If you are looking for teams that have no manager you do not need to worry about the manager table at all right?
If you wanted to find managers that do not have a team then you would need to find records in the manager table that do not have a match in teams. You can do that with a join or an exists.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 26, 2012 at 3:08 pm
yes, i see what you mean regarding the first one
however im stuck with how to use JOIN on the second one
January 26, 2012 at 3:10 pm
Don't use join. Look up IN or EXISTS and use the NOT IN/NOT EXISTS check.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2012 at 3:33 pm
something like this?
SELECT Manager_ID FROM tbl_Manager
January 26, 2012 at 3:34 pm
something like this?
SELECT Manager_ID FROM tbl_Managers
WHERE Manager_ID NOT IN
( SELECT Manager_ID FROM tbl_Teams WHERE Manager_ID IS NULL)
January 26, 2012 at 3:44 pm
Close, but think about it logically. You want the list of managers that aren't assigned teams. to do that, you first get the list of managers that are assigned teams (your subquery) and use that as a filter (the not in).
Does your subquery get the list of managerID for the teams? If you run it separately, does it list managerIDs?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2012 at 2:39 am
If i wanted to Assign the Manager without a team to the team without a manager on a 1 year contacts
ive got as far as ?
UPDATE tbl_teams SET
Manager_ID = 'Alan Curbishley'
FROM tbl_managers
Where
or is this wrong as well ? not sure how to use update from another table
January 27, 2012 at 2:52 am
SET Manager_ID = 'Alan Curbishley' will attempt to set the managerID field to the literal string 'Alan Curbishley', and will likely fail due to data types
Start with a select, how would you get a resultset with the manager_ID you want assigning? How would you get the team you want him assigned to?
p.s. You are reading up on these In Books Online (the SQL help), right?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply