Help in writing a statement

• des_vergara

  Newbie

  Points: 2

  More actions

  November 21, 2012 at 10:08 am

  #267165

  Hi need help writing a sql statement to show which departments did not submit any department goals.

  I have two tables name Department and Department Goals.

  Please help...

• Thinknook

  SSCrazy

  Points: 2531

  More actions

  November 21, 2012 at 10:23 am

  #1561315

  You'll need to provide more information, such as table structure and what have you tried so far.

  Check-out the link below (in my sig) for posting guidelines.

  -----------------
  ... Then again, I could be totally wrong! Check the answer.
  Check out posting guidelines here for faster more precise answers[/url].

  I believe in Codd
  ... and Thinknook is my Chamber of Understanding

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 21, 2012 at 11:06 am

  #1561330

  des_vergara (11/21/2012)

  ---

  Hi need help writing a sql statement to show which departments did not submit any department goals.

  I have two tables name Department and Department Goals.

  Please help...

  without more detaisl, all i can suggest is the basic syntax:

  SELECT *

  from SomeTable

  WHERE SomeID NOT IN(SELECT SomeID

  FROM AnotherTable

  WHERE SomeID IS NOT NULL)

  SELECT *

  from SomeTable

  LEFT OUTER JOIN AnotherTable

  ON SomeTable.SomeID = AnotherTable.SomeID

  WHERE AnotherTable.SomeID IS NULL

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• des_vergara

  Newbie

  Points: 2

  More actions

  November 21, 2012 at 11:22 am

  #1561337

  Thanks.. I used the left outer join

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  November 21, 2012 at 12:29 pm

  #1561378

  Based on the info provided:

  -- This table has the name of every department

  DECLARE @dept TABLE (

  deptNbr int primary key,

  department varchar(10) NOT NULL );

  -- This is where department goals are stored

  DECLARE @deptGoals TABLE (

  goalID int identity primary key,

  deptNbr int NOT NULL,

  deptGoal varchar(20) NOT NULL );

  -- Everyone has goals except for "Help Desk"

  INSERT INTO @dept (deptNbr,department) VALUES

  (1,'Sales'),(2,'Accounting'), (3,'Help Desk');

  INSERT INTO @deptGoals (deptNbr,deptGoal) VALUES

  (1,'Sell More'),(2,'Add Stuff');

  -- The Query (the noGoals CTE has the deptNbr(s) you want)

  ;WITH noGoals AS (

  SELECT deptNbr FROM @dept

  EXCEPT

  SELECT deptNbr FROM @deptGoals )

  SELECT d.department FROM @dept d

  JOIN noGoals x ON x.deptNbr = d.deptNbr

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001