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
849 Views

Similar Articles

[PageSpeed] 47

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:

Query Problems
I have a table of ecards that is populated from a website and includes receiver, sender, and Team leader. The problem I am having is names can be entered into the table like this "Tom Thumb", but Team leader is a dropdown menu that is constant and lists name like this: "Tom S. Thumb". When I make a query to find all the members of Tom S. Thumb's team, I get everyone except Tom Thumb, who is member too. Tom Thumb has his own higher team leader, so I can't change that. How do I get Tom Thumb to be included when I enter "Tom S. Thumb" as Team Leader? ...

Excel web query returns no data
When I enter a web addreess into IE it shows the page When I set up a web query in Excel it allows me to select the relevant tables, but then returns no data The same Excel query worked on previuous versions of Excel Web address for query http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBM DataServlet?param1=T_CNQPS- 4&param2=&param3=&param4=&param5=2001-04-01&param6=* Any help appreciated ...

The Settings button keeps disappearing
Hi, I'm having a weird problem when I access CRM using the web client. When I access the site, I see all the buttons (Workplace, Sales, etc) except the Settings button. If I reboot, the button is back. My security role is system Admin. Any idea what is wrong? Thanks! Simon Are you running the Microsoft CRM Outlook Desktop client in parallel? Frank Lee, Microsoft CRM MVP Workopia, Inc. http://microsoft-crm.spaces.live.com "Simon Renshaw" wrote: > Hi, > > I'm having a weird problem when I access CRM using the web client. When I > access the site, I s...

Query to Mail Merge issue
I am running Access2003 on WinXP SP2. I send out dues letters for members of a retiree group. When I try to do a mail merge to my Dues Query all goes fine except the telephone number in the query which is (555)555-1234 comes out 5555551234. If I copy the query into an excel spreadsheet and use that as the datasource for the mail merge it works fine. Is there something I need to do to format the query so it merges with the right format. Thanks Billa In the query format the field to include the parentheses and dash. In query design view, instead of the field name put an expression alo...

Public Folders: How to set up Auto-Reply in EX2003?
Hi guys, I've configured a public folder to receive mail from the Internet, and also set up an auto-reply rule using the Folder Assistant to reply when an e-mail is sent to the folder. The folder will auto-reply just fine if I e-mail it from within the Exchange organization, but if folks from the Internet e-mail the folder, it doesn't reply. Any ideas? Thanks! Jeff I ended up figuring out what the problem was. Just in case anyone else has this problem, what I needed to do to fix it was: - Open Exchange System Manager > Global Settings > Internet Message Formats - Right-c...

Export query to Excel with formated heading
When export from query to Excel, I'd like to turn some columns into heading row on top of Excel sheets how to turn this: LastName FirstName SID Section Instructor Doe John 1234 0545 Su Doe Jane 2345 0545 Su into this? Class Roster Instructor: Su Section: 0545 LastName FirstName SID Doe John 1234 Doe Jane 2345 I think one way of achieving this would be to create a report in Access where you would group under instructor with the students in the detail section. From the report you can then use the analyze with Ex...

Setting .doc default to .doc, not .dox
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel How do i do this for all new documents? > How do i do this for all new documents? I meant .doc instead of .docx Word> Preferences> Save, choose Word 97-2004 Document (.doc) from the Save Word files as: list - just keep in mind that you'll be constantly working in Compatibility Mode which will prevent the use of some of 2008's features. HTH |:>) Bob Jones [MVP] Office:Mac On 4/23/08 7:50 PM, in article ee99690.-1@webcrossing.caR9absDaxw, "dbdbdb@officeformac.com" <dbdbdb@officeform...

Printing
Hope you folks can help me out with a strange one. I have several worksheets formatted in exactly the same way as follows: Col A - width 4 Col B - hidden Col C - width 4 Col D - Width 108 Col E - Width 3 Col F - Width 11 Col G - Hidden Col H - Width 11 & Empty My print range should be Cols A:G (I have used page setup to set the scaling to fit 1 page wide by [blank] pages tall, thus each sheet will print as many pages as required depending on number of rows] When I have the print range set to A:G only columns A:E show on the print preview (and also on the actual print out) and when I m...

Setting directory for file save in macro
Hi Thanks to a generous poster I now have a great bit of code to use in a macro for Excel which saves a text file (I'm no VBA programmer so this was really helpful), i.e.: Open "Test.txt" For Output As #1 (rest of code) My problem now is this: The macro saves test.txt to Excel's current active folder, rather than the folder that the current workbook is contained in. For example, if I last saved an Excel workbook to c:\workbooks\, and the workbook that I have open exists in c:\workbooks\workbook1\, when I run the macro it saves the text file in c:\workbooks\. If I ...

Trying to set up account, geting nowhere FAST!
From: "Lenora" <lmandre@windstream.net> Subject: Understanding what Microsoft Communities Date: Friday, September 14, 2007 5:41 PM Hello out there! What is Microsoft Communities all about? Is it something that comes with no charge when you get Microsoft Vista? This is the 4th computer I've had (besides ones at work). First a Compaq that I loaded everything I could from work, the computer guy gave me stuff...did work at home, brought it back to work, and visa versa. Second was a re-built computer that was given to me by a man in the town we had moved to...out of the g...

Query Criteria issue
Access 2003: Trying to create a demographics report that will allow the following. Gender example On the form "frmStudentDemographics" is a drop down field named "cboGender" When the user makes a selection "Male" a report is generated for only Males. But in some cases the user will want to run a report for both (all) genders. So I added a check box next to the drop down named "ckAllGender" My idea is that if that box is checked then the report will show information on Both (all) genders. So in the query that generates the report, in the c...

Spontaneous 'Compile Error' In Queries
I've got a half-dozen queries that contain this expression: IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName]) Haven't touched any of them, but suddenly they were all throwing "Compile error. in query expression 'IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName])'." tblIssuer.IssuerID is still there in a link. My kneejerk was to somehow force recompiles of all the queries, so I did a Compact/Repair. After the compact/repair, all was well: the errors went away. C...

Setting up Exchange 2003
I had this problem the other day while extending the schema in preparation for an upgrade to Exchange 2003. I searched through a lot of databases and search engines, and found that a lot of people have had this problem, but none of the forums led to a clear cut answer to the issue. So, I thought I'd share my solution to try and help others out, and save them some time and frustration. I was ready to call Microsoft support right before I figured this out. While running forest prep (setup /forestprep), setup failed with Error code 0XC103798A. It then specified to check the Exchange inst...

Do you need MS Access to query on an Access table?
I'm asking these questions because I have looked at a lot of stuff in the discussion groups and still confused. I am fairly good at Excel programming but haven't been able to get Excel and Access to talk. First, I have Office 2003 Professional at work and Office 2003 without Access at home. I want to work on developing Excel programming at home which will get data from Access to Excel with either MS Query or with programming. Is it possible to just have the .mdb files on the home computer for Excel to work with, or do I need the Access program too? The info I want ...

Combining two types of functions. Select Query and DLookup
I would like to write a code that performs the following action: 1. If the value in a comboBox Project Number is 19912 2. Perform a select query on comboBox Task Number and comboBox National Site ID 3. If the value in a comboBox Project Number is not 19912 4. Perform a select query on comboBox Task Number and a DLookup () on comboBox National Site ID I tried the code below but it's not working. Any ideas on how to do this? If Me.cmbProjectNumber.Value="19912" then cmbNationalSiteID.RowSource = "SELECT DISTINCT [National Site ID] FROM [InScope Table] WHERE [Proje...

Set Application Page permissions in WSS 3.0
Example: Using the HelpDesk template, how can I apply permissions to Service Rep and Mgr Home pages? TIA! -JS ...

hierarchical data
Now that Jet is being developed in-house by the Access dev team can we expect to see some movement on its querying capabilities. I for one would love to see some functionality to better aid in representing hierarchical data. SQL Server 2005 introduced "Recursive CTE's" will Access follow suit with a similar concept? If I could request only one future feature this would be it! ...

Getting query data for a report
If I have the query "UniqueCounts" and following query results: Count ServicesType 2 Food Basket 99 Men's Dorm 84 Personal Needs 5 PN Family Pack 14 Program Dorm 7 Temporary ID What would I put in a text field in a report to get the query results from Count for the ServicesType "Men's Dorm". (Which should be the value 99.) Thanks! you would want to put in 'Men's Dorm' as the criteria in the query....on in a new query that is applied to this query ...

Lock sets of cells in a row and sort by date linked to each set
I have a set of qualifications linked to a name that need to be presented in a row A = Name B = Qual Type 1 C = Expiry Date (eg 29/01/12) D = Qual Type 2 E = Expiry Date (eg 17/12/12) F = Qual Type 3 G = Expiry Date (eg 25/06/11) H = Qual Type 4 (has no expiry date) I want to sort each qualification into 'date expiry' order keeping it connected to the relevant Qual Type. Therefore, I want Qual Type 3 with it's exipiry date to move into Column B & C, Qual Type 1 with it's exipiry date to move into Column D & E and Qual Type 2 with it's exipiry da...

problem in a form from crosstab query
Hi, I have a form from a crosstab query. On that form, I've added some fields to make "on the fly" calculations but they remain blank if one of the values returned from the query and used for calculation is = 0. What I can do? Thks in advance Use Nz() around each field, to replace null with zero. For example, instead of: =[A] + [B] + [C] use: =Nz([A],0) + Nz([B],0) + Nz([C],0) Here's another suggestion for getting a row total in a crosstab: http://allenbrowne.com/ser-67.html#RowTotal -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Acces...

What security setting is it that allows a user to change
Hi, What is the security setting that allows a user to change a Great Plains Form thus creating a forms.dic file on the server? We are using Great Plains 7.5, citrix servers on windows 2000. I am not sure the security is in Great Plains, it is probably on the citrix box but I am not sure. Any help would be greatly appreciated. Thanks, Connie Connie You can turn off access to the Modifier using either standard security (Setup >> System >> Security), or Advanced Security (Setup >> System >> Advanced Security). I would use Advanced Security as it is a more po...

Setting row global row height in Publisher
I'm trying to set a global row height for a table in Publisher. The method used in Word doesn't work (Table>Table Properties>Row Tab, etc. These options aren't even available on the drop down menu. Why does this sort of thing have to be soooo different in Publisher? Because Publisher is a page layout program... Word is more suitable for documents. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Box" <Box@discussions.microsoft.com> wrote in message news:75E3941B-961F-486C-9010-FEBE566FC800@microso...

Setting Up AOL account in Windows Mail for AOL.com account
I wish to configure my E-Mail Account into Windows Mail for AOL.com account.I wish to know the correct IMAP/POP3 and SMTP details etc. AOL would have that info. These instructions for OE will work for Windows Mail... http://www.postmaster.aol.com/imap/express.html -- Dave N. MS-MVP (Mail) Windows 7 Ultimate http://download.live.com/wlmail "sudhirbanerjee@aol.com" <sudhirbanerjee@aol.com@discussions.microsoft.com> wrote in message news:F022D246-F34F-43F0-B732-477E9EE042C6@microsoft.com... > I wish to configure my E-Mail Account into Windows Mail...

Can there be too many sub queries within a single query?
I am trying to combine the results from more than 15 queries into a single query in an attempt to save opening 15 different queries individually. The query runs successfully with the exception of the 15th, or last, query returning null cells. If I pull that sub query by itself it does return the correct data however. Has anyone experienced this in their projects? Thanks for your help, Rick I do not know as there is a limit to the number of subqueries but I read there is a limit to the total number of characters in the complete SQL statement. That would include subqueries,...

Outlook Client
Hi Is there a way to lockdown the settings on the Synchronize tab (under the CRM | Options menu) so users cannot change them? In particular the options which specify what entities get synchonized from CRM to Outlook. TJ Hi, Unfortunately I don't think you can do this - at least not unless you can do some programming that can do it. We are also hoping that this area will be improved, beacuse the way it works is just rubbish. It's just too cumbersome to modify/add filters from an admin point of view and it's not something the user should be able to mess with. I know that C...