Retrieving From One Table Where Not In Another Table
Feb 19, 2004
I have two tables, contacts and deleted_contacts. I would like to pull a list of names from contacts where that contact_id does not exist in deleted_contacts.
This what I have tried that does not work:
SELECT first_name, last_name FROM contacts WHERE created_by_user_id = '" + uid + "' AND contact_id NOT EXISTS (SELECT * FROM deleted_contacts);
I have nine type of buttons, EnrollAmtBTM PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITIONprotected void CollectAgencyInformation() { WebLibraryClass ConnectionFinanceDB;ConnectionFinanceDB = new WebLibraryClass(); string SQLCONDITION = "";string RUN_SQLCONDITION = ""; SessionValues ValueSelected = null;int CollectionCount = 0;if (Session[Session_UserSPersonalData] == null) {ValueSelected = new SessionValues(); Session.Add(Session_UserSPersonalData, ValueSelected); } else { ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]); }ProcPaymBTM.Visible = false;PaymenLstBTN.Visible = false; Dataviewlisting.ActiveViewIndex = 0;TreeNode SelectedNode = new TreeNode(); SelectedNode = AgencyTree.SelectedNode; SelectedAgency = SelectedNode.Value.ToString(); Agencytxt.Text = SelectedAgency; Agencytxt2.Text = SelectedAgency; Agencytxt3.Text = SelectedAgency;DbDataReader CollectingDataSelected = null; try {CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency + "'"); } catch { }DataTable TableSet = new DataTable(); TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);int IndexingValues = 0;foreach (DataRow DataCollectedRow in TableSet.Rows) {if (IndexingValues == 0) {SQLCONDITION = "where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'"; } else {SQLCONDITION = SQLCONDITION + " OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'"; } IndexingValues += 1; }SQLCONDITION = SQLCONDITION + ")"; ConnectionFinanceDB.DisconnectToDatabase();if (Dataviewlisting.ActiveViewIndex == 0) { Dataviewlisting.ActiveViewIndex += 1; } else { Dataviewlisting.ActiveViewIndex = 0; } SelectedAgency = SQLCONDITION; ValueSelected.CONDITION = SelectedAgency;
???? this is where i use to get count where in other buttons and are displayed.... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the viewRUN_SQLCONDITION = "SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION; try { CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION); EnrollAmtBTM.Text = CollectionCount.ToString(); } catch { }////this is my CollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg. "SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List" here is the functionpublic int CollectedFinaceDataCount(String SQLStatement) {int DataCollection; DataCollection = 0; try { SQLCommandExe = FinanceConnection.CreateCommand(); SQLCommandExe.CommandType = CommandType.Text; SQLCommandExe.CommandText = SQLStatement; ConnectToDatabase();DataCollection = (int) SQLCommandExe.ExecuteScalar(); DisconnectToDatabase(); }catch (Exception ex) {Console.WriteLine("Exception Occurred :{0},{1}", ex.Message, ex.StackTrace.ToString()); } return DataCollection; }
So here mu requirement request is to display only the value fronm the view i have against the agency selected Please help ASAP Thanks Santosh
I have a giant SQL query that retrieves a bunch of information from a couple of tables for use on a page, and I would like some help on constructing the SQL to get exactly what I have in mind. I have a table - called scContacts - that contains a list of contacts with detailed information. The contacts in the table are regular sales contacts, sales reps, and sales managers. Another table - called scCompany - contains a list of companies with detailed information.
For each company, we have a certain sales manager (or user) pertaining to that company as well as potentially a certain rep. The problem is that the contact tied to the company, the sales manager tied to the company, and the rep tied to the company all come from the same table. Here is the base SQL code I've used to get everything but the sales rep's name and sales manager's name:
scCompany.ID AS companyID, scCompany.companyName, scCompany.companyURL, scCompany.billToAddress1 AS companyAddress1, scCompany.billToAddress2 AS companyAddress2, scCompany.billToCity AS companyCity, scCompany.billToState AS companyState, scCompany.billToZip AS companyZip, scCompany.billToCounty AS companyCounty, scCompany.billToCountry AS companyCountry, scCompany.businessType, scCompany.phoneExt, scCompany.phoneNum, scCompany.faxNum, scCompany.minEmployees, scCompany.maxEmployees, scCompany.actionTypeMAX, scCompany.actionDateMAX, scCompany.actionTimeMAX, scCompany.statusID, scCompany.userID, scCompany.repID
FROM scCompany, scContacts
INNER JOIN scStatus ON scStatus.ID = scCompany.statusID
WHERE scgroupContacts.contactID = [insert cookie value] AND scgroups.ID = [insert cookie value] AND scCompany.ID = scContacts.companyID AND scCompany.groupID = scgroups.ID ----- END SQL CODE -----
As indicated right now, this SQL code will retrieve all of the contact and company information for a certain contact ID which comes from a cookie value (inserted in ASP). I want to get the [fName] and [lName] fields from scContacts table where the scContacts.ID = scCompany.userID, and I also want those same fields from scContacts table again where the scContacts.ID = scCompany.repID. It would be simplest and most efficient if I could do this all at once (and I'm sure it's possible). How would I change the SQL to bring in that information from the same table two more times, tying their ID's to ID's in the company table?
bobby writes "I have a Table from which i get the ID of customers(Customers are people who have written an online exam).The date of completion of the exams are stored in the corresponding table with the name of the exam.There are more than one exams and the names of the exams are stored in an other table.Now my question is how to get the date of cpletion with the given Id from the first table?"
this is a simple problem but it's just driving me mad as it's not reading from my dB. basically, I've have reviews stored in my dB and want to display them in a textbox by clicking on a button called btnReviews. I think the problem might be that there is too much text stored per row of the table (as it is a review), but I have the datatype set as text in sql. here's the simple un-errorred code I have behind the button. any ideas where I went wrong. i've a feeling it's something small but it's just taken too long to figure out.protected void btnReviews_Click(object sender, EventArgs e) { String strConn = ConfigurationManager.ConnectionStrings["conLocalDatabase"].ConnectionString; SqlConnection dbConnection = new SqlConnection(strConn); SqlCommand dbCommand = new SqlCommand("Select [ReviewC] From [Review])", dbConnection); dbCommand.Parameters.AddWithValue("Review", txtReviewView.Text); try { dbConnection.Open(); dbCommand.ExecuteNonQuery(); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { if (dbConnection != null) { dbConnection.Close(); } } }
Hi I ve a datagrid . And Two Database table in sqlServer2005. The name of the tables are 'Property' and 'userid'. My datagrid wants to retrive all records from Property table and one record from userid table. The Property table contains Propertycode, lastdate , departmentname. The userid table contains so many record along with 'id' record which my datagrid wants to retrieve. pl tel me how 2 write code for that?
The query below should be inserting unique records in the PropertyItem table which only has propertyitem, propertyid, and itemid columns, all of which are PK's in other tables. I'm not doing the insert correctly b/c it's inserting 72 records instead of 24. I really just want to automatically insert the values once I've inserted in the other tables but I'm not sure how. Maybe On Update Cascade?
--PROPERTYITEM INSERT INTO [USCONDEX_Production].[dbo].[propertyItem]( [propertyId], [ItemId]) SELECT Property.propertyId, ITEM.ItemID FROM ITEM RIGHT OUTER JOIN miamiherald ON ITEM.StartDate = miamiherald.FirstInsertDate AND ITEM.Price = miamiherald.PropertyPrice AND ITEM.Classified = convert(int,miamiherald.AdNumber) LEFT OUTER JOIN Property ON property.adprintid = miamiherald.adprintid WHERE validAD=1
Hi i have a junction table(UserGroups) which is linking my users table with my groups table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change
UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC Below is my SQL statement; SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')
I have a simple setup: 2 tables and a joining table, and want to retrieve a data set showing every possible combination of table A and table B together with whether that combination actually exists in the joining table or not.
My tables:
channels ====== channel_id channel_name
items ==== item_id item_name
channels_items (joining table) =========== channel_id item_id created
An example of the dataset I want (assuming 2 items and 2 channels, with itemA not being in channelB):
I need help with this. When I run the below script (only select) it retrives around 130K records and gives me the output within 2 mins. Whenever I try to put the same output in a temp or permanent table it takes hours. Any Idea why?
SET NOCOUNT ON
DECLARE @ImportId INT SET @ImportId = 5151
DECLARE @ResultXML XML SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName, resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP, CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage, resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType, CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode) CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
INSERT into #TResults (DealName,CUSIP,Vintage,PoolType,PaidOff) SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName, resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP, CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage, resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType, CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode) CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
Hello,I am in the midst of converting an Access back end to SQL Server Express.The front end program (converted to Access 2003) uses DAO throughout. InAccess, when I use recordset.AddNew I can retrieve the autonum value for thenew record. This doesn't occur with SQL Server, which of course causes anerror (or at least in this code it does since there's an unhandled NULLvalue). Is there any way to retrieve this value when I add a new recordfrom SQL server or will I have to do it programmatically in VB?Any direction would be great.Thanks!
I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure. Example data:ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level------------------------------------------------------------------------------------------------------------------------------------------------1 Custom Furniture 0 02 Boxes 0 03 Toys 0 04 Bedroom 1 Custom Furniture 15 Dining 1 Custom Furniture 16 Accessories 1 Custom Furniture 17 Picture Frames 6 Accessories 28 Serving Trays 6 Accessories 29 Entertainment 1 Custom Furniture 110 Planes 3 Toys 111 Trains 3 Toys 112 Boats 3 Toys 113 Automobiles 3 Toys 114 Jewelry 2 Boxes 115 Keepsake 2 Boxes 116 Specialty 2 Boxes 1Desired output:Custom Furniture Accessories Picture Frames Serving Trays Bedroom Dining EntertainmentBoxes Jewelry Keepsake SpecialtyToys Automobiles Boats Planes Trains
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost) How do i obtain this value and how would I supply it to the second INSERT statement?
well i have got a form which displays some result from the databasse, it runs fi9 in webmatrix, but when i try to run it from my main page and when i click on that button which should displays the aspx page, it gives me this error:
Server Error in '/' Application. --------------------------------------------------------------------------------
Login failed for user 'MUFADDALASPNET'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'MUFADDALASPNET'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
-------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
I have a detail table of comments. I need to grab the fist 50 characters from the comment field from this table. The kick is that I need to get the comments from all of the records in the detail table, as the comments might be less than 50 characters, so i need to pull the comments from the other comment records that apply to the master table record.
right now i have the comments in a join to the master table, but this is causing a few records to be returned....
I guess what i am looking for is a way to grab the comment value from the first record of the detail table.
I am working on creating a report which is retrieving data from a SQL 2005 database and being displayed in a C#.net web page. Presently, I am binding the dataset to a ASP:Gridview on the web page. I am currently retrieving most of the needed fields using the following SQL statement:PROCEDURE [dbo].[pr_getReportTickets] @DateCreated nvarchar(15) ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure hereSELECT tbl_Queue.num_TicketNumber AS TicketID , tbl_Users.str_Name AS Technician , tbl_Queue.str_QueueLocator AS TechNTID , tbl_Queue.dat_ReceivedRequest AS dateCreated , tbl_Queue.dat_DueDate AS DueDate , tbl_Queue.str_TaskName AS TicketTitle , tbl_Queue.str_Requestor AS RequestorNTID --, I need most current dat_TimeStamp from tbl_Notes here for this record --, I need most current int_PercentComplete from tbl_Notes here for this record --, I need to sum up all of int_MinutesWorked fields from tbl_Notes here for this record , MasterEmp.dbo.fn_FormatFullName(tbl_employee.str_fname, tbl_employee.str_lname) AS RequestorName , tbl_Queue.str_TicketType AS TicketType , tbl_Status.str_TaskStatus AS TicketStatus , tbl_Severity.str_Priority , tbl_Complexity.str_Complexity , tbl_Severity.str_Priority + N' / ' + tbl_Complexity.str_Complexity AS Priority , tbl_Queue.dat_CompleteDate as DateCompleted FROM dbo.tbl_Queue LEFT OUTER JOIN MasterEmp.dbo.tbl_employee AS tbl_employee ON dbo.tbl_Queue.str_Requestor = tbl_employee.str_ntid LEFT OUTER JOIN tbl_Users ON tbl_Queue.str_QueueLocator = tbl_Users.str_ntid LEFT OUTER JOIN tbl_Status ON tbl_Queue.num_Status_CD = tbl_Status.num_Status_CD LEFT OUTER JOIN tbl_Severity ON tbl_Queue.str_Severity = tbl_Severity.str_Severity LEFT OUTER JOIN tbl_Complexity ON tbl_Queue.int_ComplexID = tbl_Complexity.int_ComplexID WHERE (dbo.tbl_Queue.dat_ReceivedRequest > CONVERT(DATETIME, @DateCreated, 102)) ORDER BY TicketType, tbl_Queue.str_Severity I also have another table called tbl_Notes. This table contains an unlimited quantity of records for every “num_TicketNumber�. This table contains the following fields: num_TicketNumber, str_TechRep, str_Notes, dat_TimeStamp, int_PercentComplete and int_MinutesWorked I need to add two more fields to the query, but I do not know how to tell the SQL statement how to retrieve the data for the specfic record and inbed the fields into the Dataset being returned from the database to the web page.1) The first field I need is the most current recorded Date field from the tbl_Notes table for the each of the records returned in the above SQL statement. Similar to: SELECT TOP (1) dat_TimeStamp FROM tbl_Notes WHERE(num_TicketNumber = xxxx) ORDER BY dat_TimeStamp DESC2) The other field I need to return with the Dataset is the sum of the int_MinutesWorked for each of the tickets being retrieved. Similar to: SELECT SUM(int_MinutesWorked) AS TotalMinutesFROM tbl_Notes WHERE (num_TicketNumber = 49)
I am currently investigating the best way to handle partition swapping where the partition scheme is built on several file groups. In order to swap partitions, both partitions need to be on the same file group. In addition, the file groups and partitions are automatically updated once a month meaning that there is a scheduled job that adds a new file group, file group files, and updates the related partition objects.If I am forced to create a new non-partition table on the same file group as the partition of the target table, what would be the best way to create the target table without hard coding the full table definition and clustered index? I know that I can query the systables and syscolumns views to reconstruct a basic SQL statement to build the table definition and views Is there a SQL Server function that I can use to just give me the object definitions? Unfortunately, the OBJECT_ DEFINITION function doesn't apply to tables or indexes. URL....
Can anyone help me on this... when i select data from table using select statement it takes huge amount of time....The table contains 7 million entries and when i select by mentioning a criteria it takes around 45 secs..The system has 4GB RAM and Dual Processing CPU. The select statement does not contain any grouping and all..
Will it take this much time to retrieve data.?. The table does include an indexed field, So can anyone help me on the different things i can do to make the retrieval faster?
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below: EXEC sp_addlinkedserver @server = 'test1', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'testsource' exec sp_addlinkedsrvlogin @rmtsrvname = 'test1', @useself = 'false', @rmtuser='sp', @rmtpassword='sp'
When I execute select * from test1...COUNTRY I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table." The 'sp' user I am connecting is the owner of the table. What could be the problem ? Thanks a lot.
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
I am having a table called as status ,in that table one field is there i.e. currentstatus. the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table. I can do programatically. but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual (empno, accrued_vacation, accrued_sick_effective_date, accrued_sick, import_date)
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing: dbo.Categories dbo.CustomerCustomerDemo dbo.CustomerDemographics dbo.Customers dbo.Employees dbo.EmployeeTerritories dbo.Order Details dbo.Orders dbo.Products dbo.Regions dbo.Shippers dbo.Suppliers dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing: dbo.Categories New Table... Modify Open Table Script Table as |> CREATYE To |> DROP To |> SELECT To |> INSERT To |> New Query Editor Window File.... Clipboard UPDATE To |> DELETE to |> From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance, Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
