how to set up a query

I am using sql server 2005 express and have 3 tables

Table1
Dept_Id  (primary key)
Dept_Name

Table2
Employee_Id (primary key)
Dept_Id (foreign key with table1)
Employee_Name

Table3
WorkSchedule_Id (primary key)
Employee_Id (foreing key with table 2)
Date_To_Work (date type)

I want to list all the Departments (Dept_Name) that do not have anyone
scheduled to work on a particular date (ie  '1/20/2010' )

Any help would be appriciated.
Thanks in advance,
RABMissouri2010
0
RAB
1/20/2010 6:14:42 PM
sqlserver.server 1327 articles. 0 followers. Follow

1 Replies
903 Views

Similar Articles

[PageSpeed] 42

Try this:

SELECT dept_name
FROM Table1 AS D
WHERE NOT EXISTS(SELECT *
                  FROM Table2 AS E
                  JOIN Table3 AS W
                    ON E.employee_id = W.employee_id
                  WHERE E.dept_id = D.dept_id
                    AND W.date_to_work = '20100120');

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
1/20/2010 6:37:53 PM
Reply:

Similar Artilces:

Make Table Query
I have a Macro and at the end of the Macro I have a Make Table Query that I would like to automatically save as todays date in the current database. Does anybody have an idea on how I can do this? On Wed, 06 Jun 2007 11:40:24 -0700, dswiders@gmail.com wrote: >I have a Macro and at the end of the Macro I have a Make Table Query >that I would like to automatically save as todays date in the current >database. Does anybody have an idea on how I can do this? A date is just a field value. Saving today's date as a Table is almost certainly VERY bad design. Why do an expensive, awk...

Corstabquery or normal query
Hello, Maybe I have a langguage problem in expressing my difficulties, but here is what I wanted Normal report based on the normal query: Year 2005 No. Position Name 1, Elder Mr. A 2. clerck Mr. B 3. Deacon Mr. C Year 2006 1, Elder Mr. D 2. clerck Mr. E 3. Deacon Mr. F Year 2007...same way downward I wanted it that the year is Sideway" No. Position Name Year 2005 Year 2006 Year 2007 1, Elder Mr. A Mr. D ....... 2. clerck ...

Sum of a UNION Query
Hello, I want to sum the values together that are returned from the following two queries joined with a UNION from within the same query (instead of creating a second query that will SUM the two values that are produced from this one). I tried "wrapping" this code within a SELECT SUM, but it returns with "this is not allowing in subqueries". SELECT Count(*) AS SubCount FROM [TABLE1] WHERE (([TABLE1].USER)="JOHNSMITH") UNION ALL SELECT Count(*) AS SubCount FROM [TABLE2] WHERE (([TABLE2].USER)="EJONES"); The results are: SubCount -------------- 380 45...

template orright hand margin set up
I want to set up the right hand portion of my page to have a line down the margin and to have the word "NOTES" to appear at the top of this margin or column for each page. I write many college papers and I want the reader to have the courtesy to have a separate "NOTE" column for his own use as he/she reads the paper. Type the word "NOTES" in a text box that you anchor to the header paragraph; that way, it will be repeated on all pages using the header in question. For more, see http://sbarnhill.mvps.org/WordFAQs/AnchorToHeader.htm. -- Stefan Bl...

Append Query every day at certain time automatically
Hi, So i have a query qryStats. Basicaly i want to be able to see how the stats have changed over a period so i was thinking of turning that simple query into an Append Query so that it just add the results to a table tblStats. I tested it and it works great so i can design reports and the result is exactly what i want. Problem is im going to forget to run the query each day. What are my options? Can i write code of some kind that runs the append query each day at, lets say 16:00? YES but your db would need to be running in order for that to work. You could also create...

Script for Query-Based DL's
Pardon me if posted in the wrong place. We are scripting a large amount of OU structures and administration groups with delegation through dsacls. This is something that is repeated many times. One item that is asked to create is a query-based DL. Since it would need to be ran so many times and for consistency I am trying to find if we can have the script create the Q-B DL's. I have found scripts for dsacls and the Q-B DL's but I'm having a hard time finding how to script the creation of the DL's. Does anyone know how this would be done? Thank you, Michael ...

can I use Publisher to set up signatures of 8 pages?
I neeed to print and bind a book. I can't find any way to set up and print in sgnatures of 8 or so pages. I seem to be limited to 4 page bookfold. Any ideas? Insert, page -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "traveller" <traveller@discussions.microsoft.com> wrote in message news:9FD3B5CB-8D56-49DE-B632-06617A54EBE7@microsoft.com... >I neeed to print and bind a book. I can't find any way to set up and print in > sgnatures of 8 or so pages. I seem to be limited to 4 page bookfold. Any > idea...

Help with a simple query
Hi guys and gals, Can anyone help me with this query? I am having some issues with pulling in all names even though (it appears) I have the join correct. SELECT tblEmployee.EmpRptID, tblEmployee.UserName, Sum(tmpReports.NumOfSets) AS SumOfNumOfSets FROM tblEmployee LEFT JOIN tmpReports ON tblEmployee.UserName = tmpReports.UserName WHERE (((tblEmployee.IsCQATech)=True) AND ((tmpReports.CompleteDate) Between #2/8/2010# And #2/12/2010#)) GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName; What am I doing wrong? You have defeated the left join by applying criteria to the right s...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

Security Settings
I am having some issues w/ Security settings in Dynamics. I have some users that we are only giving the ability to enter POs and print them. We have gone through all of the sub-windows, and have verified through users creating POs that they have access to everything in the PO entry. However, when these users are logging in there is a message that says that user does not have access to this window. The error message does not list any windows and the PO entry window is the only set on the startup. The issue that accompanies this is that the system date is not automatically filled in...

How can I set up the random selection of a cell from within a ran.
I want Excel to randomly select one cell from a range of cells, but is it possible? This would be the equivalent of drawing one slip of paper from several hundred. An uncomplicated way to do this is to simply add a column filled with the RAND() function next to your column of choices. Select both columns, and sort on the Rand() column. Pick the one at the top, or bottom, as your random selection. A little more complicated procedure would be to use a formula, referring to this combination of Rand() and your list, and let this formula randomly pick an item. Place your list in an "out o...

disk mgmt query
I have a desktop system with two different sized HDs with both having the identical xp media edition installed. The problem is that windows is not allowing me to manage the pecking order. Drive "0" is being read in disk mgmt as drive D and Drive "1" is being read as drive C. In the bios upon boot up, Drive 0 is in fact being read as the primary master and Drive 1 read as the primary slave. I have tried to change the letters of these drives to drive 0 = C and drive 1 = D but a window pops up saying that I can't change the name of the boot drive. "Windows c...

Query about Last Cell reference.
I have 12 sheets, which holds monthly information. Data exists in Column A through K, row 1 down to row 500. I have a formula in cell AZ1 (done to ensure no one can find it and subsequently delete it). When I depress CTRL+END it takes me to AZ500 (as expected). The question I have is that as all my data is going to be in Columns A through K and rows 1 to 500, if I reset the Last Cell to say K500 would it achieve faster saves to a LAN?. Please note that the value from AZ1 is used in column G1 – G500. All views greatly appreciated. First, you can olnly "reset" the last cell ...

adding a Carriage Return or Line Feed character to an update query
I'm building an update query that will concatenate two fields and update the first field with the combination of the two fields. However, I would like to separate the two components with a blank line, or two carriage return or line feed characters. In Visual Basic, I'd use vbcr&vbcr to do this. How can I do this in an update query? Thanks in advance, Paul Paul wrote: >I'm building an update query that will concatenate two fields and update the >first field with the combination of the two fields. However, I would like >to separate the two...

Set print area on several pages at once in Excell Workbook?
How can I set the print area on several pages of an Excell workbook at one time instead of having to set it one page at a time? You would have to write a macro to do this. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "willow" <willow@discussions.microsoft.com> wrote in message news:6A934EF4-AF5F-49F5-9F0A-1BB51A45F66C@microsoft.com... > How can I set the print area on several pages of an Excell > workbook at one > time instead of having to set it one page at a time? When you have the workbook open, ther...

No email since set up.Loads before.Reasons why?
Recently reinstalled windows and microsoft office 2003.Generally working ok,but since microsoft outlook took over from outlook express have received no email at all.Previously receiving up to twelve a day.Dont want to mess unnecessarily with system,but need email back.What can I do? Reva With Outlook 2003 open go to Tools>E-mail Accounts. Follow the wizard to set up an account. Gord Dibben Excel MVP On Thu, 22 Dec 2005 12:06:02 -0800, Reva Tir. <Reva Tir.@discussions.microsoft.com> wrote: >Recently reinstalled windows and microsoft office 2003.Generally working >ok,b...

Allow Defaults for Tools
Would like to have the ability to set the defaults for the Tools - Options window for all CRM users and/or groups of users. Settings include Default Pane, Default Tab, Records Per Page, and Time Zone. -- Thanks, Justin ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. ...

Business Portal Order Management Item Categories Query how to chan
Hi , I am looking for some guidelines to how to change the query of the Item Categories web part on Orders page in Business Portal. This web part displays a list of Item Categories, defined in Catalogue Maintenance. by default it shows Category ID ( name of Categories ). my clients want to display the Description of category. is there a way to change the Query behind this web part ? please advise. Thanks Syed ...

Is it Possible to make a table from a query without a Make Table Q
Is it possible to make a table with a query as the record source, without using a Make Table Query. The reason that I as is that I have a rather large database that I use to create tables for each day of the month based on activity recieved from a larger database. I use one Pass-Through query as the source for 31 Make Table queries. The reason I have done this is because I need to segregate the activity by date so that I can produce daily averages over the entire month. The reason that I have had to break each one per day is the Pass Through query is huge (returning 7,000,000 + rec...

Form using a query to look up values
SELECT Residents.[Last Name], Reciepts.Date, Reciepts.[Street Number], Reciepts.[Street Name], Reciepts.Amount FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] = Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street Name]); I have a couple of combo boxes in my form that uses information from this query to populate a table. The query allows me to type in the last name in a box on my form, and then fill in the street number and street name using the drop down box. My issue is that once I fill in the form once, and go to the next line, th...

Query for random values multiple records
I have a table of names (lots of names! Too many to write individual queries for...). I want three random non-repeating numbers between 1 and 7 for each of these names. Output example: Name1 3 Name1 2 Name1 5 Name2 1 Name2 5 Name2 4 From reading on the boards I was able to come up with a way to generate output such as this: Name1 3 Name1 2 Name1 5 Name2 3 Name2 2 Name2 5 However, that will not work for what I want the random numbers for. I need a different set of each random non-repeated number set for each name. Thanks! On Mon, 16 Apr 200...

Querying using SQL statement
Hi Using an expression as follows... SELECT IDCustomer AS Code, Name, Address FROM Customers ....the AS clause doesn't work in Access 2007. Running the query, the column caption remains IDCustomer. What is the problem? Why this happens? Is there any solution (except back to 2003)? Thanks in advance for your help. Joe On Sun, 7 Feb 2010 20:31:19 -0200, "Joe" <j.l.pires@uol.com.br> wrote: >Hi > >Using an expression as follows... > >SELECT IDCustomer AS Code, Name, Address FROM Customers > >...the AS clause doesn'...

Can't set default font for envelopes
I've been able to set default fonts for Normal.doc but envelopes keeps forgetting the default. While in Mailing/Envelopes - I highlight and right click on the address I entered. I select the font I want (Trebuchet MS) and select DEFAULT at bottom left. The message: "You are about to change the default font to (Default) Trebuschet MS,. Do you want to change to affect all new documents based on the Normal Template?" I select YES - It doesn't work. And the Normal.docx has alread had the default set to Trebuchet MS. and seems to be holding. An earlier ...

How do I put a unique logo into Personal Info set on MS PUB
I want to get my own unique logo. THEN I want to put it permanently into the "logo" box in the Personal Information area so that I can frequently use it. Is this doable? Lots of help here Edit a logo for a personal information set http://office.microsoft.com/en-us/publisher/HP030713481033.aspx?pid=CH062524721033 -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Gil Fair" <gilfair@discussions.microsoft.com> wrote in message news:31A9934E-A6D9-4356-91FF-6B14831F2A6E@microsoft.com... >I want to get my own ...

OnTimer() Not Being Called after timer is set up (VS C++ 6.0)
OK, here is what I have done. I am looking to use a timer to complete some refresh operations every 2 seconds. However, I cannot get my OnTimer()function to be called. I set up a timer with the following line of code: SetTimer(1, 2000, 0); Then, I have the Ontimer function, which I created from ClassWizard as a message handler for WM_TIMER. void CProbeTalkMenu::OnTimer(UINT nIDEvent) { m_szStatus += "*"; UpdateData(); CDialog::OnTimer(nIDEvent); } OnTimer() is never called. I am afraid that I am failing to do something grossly important. Can anyone explai...