SELECT
[List1].[TITLE],
[List1].[FORENAME],
[List1].[SURNAME],
[List1].[JOB]
FROM List1
WHERE EXISTS(
SELECT [List2].[forename] FROM [List2]
WHERE [List2].[forename] = [List1].[FORENAME]
AND [List2].[surname] = [List1].[SURNAME]
AND [List2].[email] = [List1].[EMAIL]
)
I am on the right track here, aren't I?
That query will.. sort of... loop through List1, get the "current" records firstname, surname and email, and then go and see if there's any single record in List2 that contains exactly that combination of values in those fields. It's a stupid question but I'm not the brightest of sparks and BOL leaves me even more confused. I'm just worried in case it's going "well, record 34 matches the current firstname and record 96 matches the current email, so EXISTS returns true".
Assuming I've got it right, though - many of the records that I know to be duplicates have a NULL email address. And the query above seems to be automatically removing them. If there's a record in both List1 and List2 with identical firstname and surname, and both have NULL email addresses - should the query above pick it up? If not, how can I include those records?
I like the new gig a lot. Real busy, smart folks and I have been in high demand since 5 minutes after my butt hit the chair. I already have code in production.
Anyhow, we have a security situation on the sql servers I pointed out on my first day. So they want me to roll everything over to Windows Authentication and give the developers and report writers more restricted rights inside SQL Server. So they have NT Groups for different kinds of users and all of that jazz and I layed on the typical stuff about using NT groups vs individual accounts and ease of admin vs granularity of control. Well the boss came back and said he wants ease of admin and granularity of control over security. So, does anyone have any fresh thinking on turning my eitheror into an AND.
Hello everyone, this is my first time to these boards. I've been running all around for the last few days trying to solve a problem. So far, on 3 forums, we haven't been able to find a solution.
I am using MS SQL 2005 Workgroup. I have catalog of events on my site. Each event can have a little as 0 up to an unlimited amount of Photos attached to them(in a seperate table.) Here's the basic breakdown.
catalog ----------- id - PK act_name (price and other such info)
Photos ----------- id - PK path_to_photo event_id - FK(catalog.id)
What I'm trying to do is select all the events in the catalog, and order them by the number of photos they have in the photos table. The idea is to get the events that have photos at the top of the list.
here is the query that I'm using right now.
Code:
SELECTCOUNT(photos.id) AS PhotoCount, catalog.id AS item_id, catalog.company, catalog.act_name, catalog.location, catalog.price_adult, catalog.price_child, catalog.short_description, catalog.photo, catalog.children_allowed, catalog.long_description, catalog.online, catalog.act_type, event_types.act_type AS evt_type, event_types.id AS event_id, catalog.rank, catalog.length, catalog.bullets, photos.id FROM catalog, event_types, photos WHERE catalog.act_type = event_types.id AND photos.event_id = catalog.id GROUP BY COUNT(photos.id), catalog.id, catalog.company, catalog.act_name ORDER BY photos.id, catalog.id, catalog.company, catalog.act_name
Which returns the following error. Column 'catalog.location' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
No matter what I do, I always seem to get an error. I've removed the Count in the select list, and that tells me that I cannot call Aggregate functions in the Group By list.
I have the following bit of code that is an onclick event to save information in text boxes and list boxes. I cut out a bunch that was irrelevant to this because ALL items in the text boxes save fine. My biggest question that should lead to the answers for the rest of the code, is for lines 53-57... That stored proceedure does not appear to run. All it is set to do is and the code is DEFINITELY getting passed into it but it almost seems like it isnt and therefoe isnt doing anything. I get no errors, just nothing happens. I would of thought at the very least, it should run this proceedure and delete the information from the table even if it wouldnt save the new information based on the listboxes. I am not sure if I am explaining this correctly, but if anyone has any thoughts, I would greatly appreciate it. The delete proceedure that isnt actually making a changeALTER PROCEDURE [dbo].[Delete_Team_Data] ( @Code as int ) AS
DELETE from tblSectyData2 where Code = @Code DELETE from tblSectyData2 where SectyCode = @Code The actual code 1 2 3 protected void SaveChanges(object sender, EventArgs e) 4 { 5 6 string selectedEmployee = ""; 7 selectedEmployee = EmployeeList.SelectedValue; 8 9 10 11 string fName = ""; 12 //Snipped out a bunch of code relating to the text boxes 13 string secretaryCode = ""; 14 15 fName = txtFName.Text; 16 //Snipped out a bunch of code relating to the text boxes 17 secretaryCode = TeamList2.SelectedValue.ToString(); 18 int selemp = Convert.ToInt32(selectedEmployee); 19 20 21 22 23 String Conn = (string)Application["Facebook"]; 24 SqlConnection IntranetConnection; 25 SqlDataReader IntranetReader; 26 IntranetConnection = new SqlConnection(Conn); 27 //SaveEmpChanges works properly. 28 SqlCommand SaveEmpChanges = new SqlCommand("Exec dbo.Edit_Employee_Data '" + prefix + "','" + lName + "','" + fName + "','" + mName + "','" + pos + "','" + dept + "','" + directdial + "','" + ext + "','" + fax + "','" + hphone + "','" + cphone + "','" + partner + "','" + timekeeper + "','" + notary + "','" + practice + "','" + saddress + "','" + sphone + "','" + lnl + "','" + bar + "','" + oemail + "','" + haddresscom + "','" + haddress + "','" + hcity + "','" + hstate + "','" + hzip + "','" + school + "','" + degree + "','" + status + "','" + floor + "','" + code + "','" + email + "','" + language + "'", IntranetConnection); 29 //Delete Team does NOT work 30 SqlCommand DeleteTeam = new SqlCommand("Exec dbo.Delete_Team_Data '" + selemp + "'", IntranetConnection); 31 //GetEmployeeType does work 32 SqlCommand GetEmployeeType= new SqlCommand("Select EmpType from tblMain2 where Code = '" + selectedEmployee + "'", IntranetConnection); 33 34 IntranetConnection.Open(); 35 IntranetReader = SaveEmpChanges.ExecuteReader(); 36 IntranetReader.Close(); 37 IntranetConnection.Close(); 38 39 40 41 42 int count = 0; 43 string LinkT = ""; 44 string LinkT2 = ""; 45 46 string etype = ""; 47 48 lselemp.Text = selectedEmployee; 49 //Basically, while the page loads fine, it does NOTHING below this line... Ive comments out sections, I have also put in a bunch of labels in to show the variables being passed, it all seems fine. 50 51 52 53 IntranetConnection.Open(); 54 IntranetReader = DeleteTeam.ExecuteReader(); 55 IntranetReader.Close(); 56 IntranetConnection.Close(); 57 58 59 IntranetConnection.Open(); 60 IntranetReader = GetEmployeeType.ExecuteReader(); 61 while (IntranetReader.Read()) 62 { 63 etype = IntranetReader["EmpType"].ToString(); 64 } 65 IntranetReader.Close(); 66 IntranetConnection.Close(); 67 68 int end = Convert.ToInt32(TeamList2.Items.Count); 69 string teamcode = ""; 70 71 while (count < end) 72 { 73 74 teamcode = TeamList2.Items[count].Value.ToString(); 75 76 if (etype == "S") 77 { 78 LinkT = "Secretary"; 79 LinkT2 = "Works with"; 80 } 81 82 else if (etype == "O") 83 { 84 LinkT = "Works with"; 85 LinkT2 = "Secretary"; 86 } 87 88 89 90 //This command does not work 91 SqlCommand saveTeam = new SqlCommand("Exec dbo.Add_Team_Data '" + selemp + "','" + teamcode + "','" + LinkT + "','" + LinkT2 + "'", IntranetConnection); 92 93 94 IntranetConnection.Open(); 95 IntranetReader = saveTeam.ExecuteReader(); 96 IntranetReader.Close(); 97 IntranetConnection.Close(); 98 99 100 count++; 101 } 102 103 104 105 106 Response.Redirect("ManageEmployeeDirectory.aspx"); 107 } 108
I have an interesting query problem. The result needs to be a single recordset as from a select statement, here's the prerequisites:
Tables: tShip tPerson tAddress tCertificate, has a field dtmExp (datetime)
where each Ship has one or more Persons which have one or more Addresses and each ship has one or more certificates.
Now, I need the query to return all ships, all persons and all addresses for those persons BUT ONLY the certificate with the latest expiration date (sort of like the result from a 'select top 1 from tCertificate order by dtmExp desc')
How can this be packed into one query? using inner joins on all tables will return multiple rows for each certficate for a ship, which it must not.
I'm trying to create a "Self-Referential, Many-to-Many" relationship under the Database Diagrams section in Sql Server 2005 express and I'm having a heck of a time figuring out where to click and edit to create what I want.
The url below links to the pictorial represenation of what i'm trying to create. Any help is greatly appreciated. Thanks! http://www.communitymx.com/content/source/A1A63/diag.gif
It's a dream of human beings to build machines that can think andbehave like human beings. The most important part of of such a machineis an artificial mind that can emulate the cognitive processing ofhuman mind.This book, "Next Generation Artificial Intelligence, Artificial Mind -Part One - Basic Architecture and Cognitive Structure" introduces abasic artificial mind architecture and computational model forcognitive processing. Inside the book, three important cognitiveprocess modeling components, mental objects network (MON),associative-learning mechanisms and a concept formation principle areintroduced. Based on the architecture and the computational model, onecan develop his own model of artificial mind according to his ownspecific requirements.The first edition of Artificial Mind - Part One is now available forpurchase from the author's personal web site. The price of the e-bookis USD7.00 (seven US dollars). An evaluation edition of this e-book isalso available for download from the web site.The author's personal web site:http://www.geocities.com/tomwingmak/
For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table. -------Employees table-------- EmployeeID . . . . . . . . . . int Name . . . . . . . . . . . nvarchar(50) ManagerID . . . . . . . . . . . int
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager. How can that be achieved?
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1) begin select @errno = @errno | 1 end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE dbo.t1 EXISTS TABLE : nested iteration. Table Scan. Forward scan. Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1 SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01) WHERE f1 = @p1 ) SET ROWCOUNT 0
Hello, I am trying to create a table if one with the same name does not exists. My code is: Dim connectionString As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|PensionDistrict4.mdf;Integrated Security=True;User Instance=True" Dim sqlConnection As SqlConnection = New SqlConnection(connectionString) Dim newTable As String = "CREATE TABLE [" + titleString + "Comments" + "] (ID int NOT NULL PRIMARY KEY IDENTITY, Title varchar(100) NOT NULL, Name varchar(100) NOT NULL, Comment varchar(MAX) NOT NULL, Date datetime NOT NULL)" sqlConnection.Open() Dim sqlExists As String = "IF EXISTS (SELECT * FROM PensionDistrict4 WHERE name = '" + titleString + "Comments" + "')" Dim sqlCommand As New SqlCommand(newTable, sqlConnection) If sqlExists = True Then sqlCommand.Cancel() Else sqlCommand.ExecuteNonQuery() sqlConnection.Close() End If I keep getting a "Input String was incorrect format" for sqlExists? I am new to Transact-SQL statements, any help would be appreciated. Thanks Matt
ok i have 2 tables---one table is name CourseInformation with a field named Instructor and the data in there looks like this 'John Doe'. My other table Instructors has 3 fields InstructorName, LastName, FirstName. I am grabbing the Instructor field from CourseInformation and breaking up the names and inserting them into my instructors table as follows.. Insert into Instructors(InstructorName, LastName, FirstName) (Select Distinct ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor)))+', '+ SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1), ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor))) as LName, SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1) as FNamefrom CourseInformation where NOT EXISTS(Select * from instructors where LastName = LName and FirstName = FName) AND Instructor is not null)
Only problem is, I cant get the where not exists clause to work right(of course that wont work what i have cuz the LName and FName columns dont exist, i just did that for demo purposes). I dont want duplicate instructors in there..how can i accomplish this..is there a better way to rewrite my query? Any help is appreciated.
HI All,Which is best among the two 1) NOT IN or 2) NOT EXISTS .If the query is Select col1 from tab1 where col2 NOT IN (Select col 3 from tab2 where cl3=0) OR Select col1 from tab1 where col2 NOT EXISTS (Select col 3 from tab2 where cl3=0)
Hi, Is there any way to check whether a column is there in the table, if it is there i need to drop it through script.
i'm looking for the script, something like this..
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tbl_Product_Tbl_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Tbl_Product] DROP CONSTRAINT FK_Tbl_Product_Tbl_Products GO
In the same way i need to check for a column and drop it through script. Any help would be greatly appreciated. Thanks in advance.
Im having a problem with the following can anyone spot how i can fix it? I dont think it likes the begin statement but without it, it has a declare issue.
IF EXISTS (SELECT 1 FROM snapevent.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='FastEp_Snap_OD_Acc' + preports.dbo.f_filedate(getdate()) begin declare @CMD nvarchar(300) Set @CMD = 'drop table Snap_OD_Acc_' + preports.dbo.f_filedate(getdate()) --print @CMD exec (@CMD) end
The secenario is that, A application calls the SP with a parameter(login), and a string of datas ( Acctid level1 level2; Acctid level1 level2; .......)
UserID AcctID Level1 level2 test testee N Y
the SP have to get the first string of data and check if the Acctid exists or not. If yes then update else insert.Then get then the second string of data and check if the Acctid exists or not. If yes then update else insert.
After checking all the strings , it have to check if any Acctids other than acctid mentioned in the string exists in the table for that login, then delete those rows
I'm trying to perform an insert query on a table, but I also want to check to see if the record exists already in the table. It should be fairly simple, but I'm having a time of it. Should be something like:
select * from users u inner join miamiherald m on u.emailaddress = m.advertiseremail where not u.emailaddress not exists <<< (???)
If it does exist, I then want to retrieve two columns from it. HELP!!
The following works in SQL 2005 but NOT SQL 2005 Compact Edition:
IF EXISTS (SELECT ID FROM Court2 WHERE BookingDate = '2007-05-28') UPDATE Court2 SET T1100 = 52 WHERE (BookingDate = '2007-05-28') ELSE INSERT INTO Court2 (BookingDate,T1100) VALUES ('2007-05-28',52)
In CE I get the following error:
There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]
I can't find where the problem is - can someone help.
Dear all, i am trying to improve the performance of stored procedures and functions in that in key word is there i have to replace with exists.which one will give better performance.
Dear all, i'm trying to replace in with exists... i've changed the query but still i'm getting error. please let me know where i'm missing
DELETE FROM table25 WHERE Col6='SET' AND Col1 IN (SELECT s.Col1 FROM Table24 s WHERE Col2='some_data1' AND Col3='some_data2' AND Col4='somedata3')
DELETE FROM table25 r WHERE r.Col6='SET' AND exists (SELECT s.Col1 FROM Table24 s WHERE r.col1=s.col2 and Col2='some_data1' AND Col3='some_data2' AND Col4='somedata3')
thank you very much
Vinod Even you learn 1%, Learn it with 100% confidence.
There's an error in this SQL statement somewhere, but I can't seem to find it. Here's what I'm trying to do:
I have a list of states, and each state is assigned an ID For each state, I have some data Given a state name, find the state's ID If the state ID exists, output the data for that state If the state ID is not in the database, output the data for all states
Here's my tables:
-------------------------- States -------------------------- ID | Name --------------------------
-------------------------- Data -------------------------- StateID | StateData --------------------------
Here's my SQL:
ALTER PROCEDURE sproc_GetStateData ( @State varchar ) AS DECLARE @StateID int
IF EXISTS(SELECT @StateID = ID FROM States WHERE Name = @State) BEGIN SELECT D.StateData FROM Data AS D INNER JOIN States AS S ON D.StateID = S.ID WHERE (D.StateID = @StateID) END ELSE SELECT D.StateData FROM Data AS D INNER JOIN States AS S ON D.StateID = S.ID RETURN
Hi,I need some help doing a simple query. Here is what I haveSELECT Category_Id, Category_nameFROM AG_Category WHERE Auth_Logic_Id = 244And Category_ID(I want category_id to not have a '.' in it. Basically returningeverything above but checking also the category_id so it doesn't returnone where category_id have a . in it)Thanks:D
I need to insert records into the table parSalesDetailModifier fromOLDparSalesDetailModifier where (1) those records DO NOT exit inparSalesDetailModifier and (2) those records have a parent record inparSalesDetail.When I run the below query I get the error message that I am violatingthe Primary Key Constraint for parSalesDetailModifier. In other words,it's trying to insert a record that does exist.Also posted below are create and insert startements for thte tables.If someone would be kind enough to show me what I am doing wrong, I'dreally appreciate it.Thanks,Jennifer-------------------------------- STORED PROCEDURE-------------------------------CREATE Proc LoadModifier2@S datetime,@E datetimeASINSERT INTO ParSalesDetailModifier(parSalesDetailModifierID,parSalesHdrID,parSalesDetailID,ModifierType,POSModifier,Condiment,CondimentPrice,UnitNumber,BusinessDay)SELECTOLD.parSalesDetailModifierID,OLD.parSalesHdrID,OLD.parSalesDetailID,OLD.ModifierType,OLD.POSModifier,OLD.Condiment,OLD.CondimentPrice,OLD.UnitNumber,OLD.BusinessDayFROM OldParSalesDetailModifier OLDWHEREEXISTS( SELECT DET.parSalesHdrID,DET.parSalesDetailID,DET.UnitNumber,DET.BusinessDayFROM ParSalesDetail DETWHERE OLD.parSalesHdrID = DET.parSalesHdrIDANDOLD.parSalesDetailID = DET.parSalesDetailIDANDOLD.UnitNumber = DET.UnitNumberANDOLD.BusinessDay = DET.BusinessDay)ANDNOT EXISTS( SELECT NEW.parSalesHdrID,NEW.parSalesDetailID,NEW.parSalesDetailModifierID,NEW.UnitNumber,NEW.BusinessDayFROM ParSalesDetailModifier NEWWHERE OLD.parSalesHdrID = NEW.parSalesHdrIDANDOLD.parSalesDetailID = NEW.parSalesDetailIDANDOLD.parSalesDetailModifierID = NEW.parSalesDetailModifierIDANDOLD.UnitNumber = NEW.UnitNumberANDOLD.BusinessDay = NEW.BusinessDay)AND OLD.BusinessDay between @S and @E-------------------------------- END STORED PROCEDURE--------------------------------------------------------------- CREATE TABLES-------------------------------CREATE TABLE [parSalesDetailModifier] ([ParSalesDetailModifierID] [int] NOT NULL ,[parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[ModifierTYPE] [int] NULL ,[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CondimentPrice] [money] NOT NULL ,[UnitNumber] [int] NOT NULL ,[BusinessDay] [datetime] NOT NULL ,CONSTRAINT [PK_parSalesDetailModifier] PRIMARY KEY CLUSTERED([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID],[ParSalesDetailModifierID]) WITH FILLFACTOR = 70 ON [PRIMARY] ,CONSTRAINT [FK_parSalesDetailModifier_parSalesDetail] FOREIGN KEY([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID]) REFERENCES [parSalesDetail] ([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID])) ON [PRIMARY]GOCREATE TABLE [OLDparSalesDetailModifier] ([ParSalesDetailModifierID] [int] NOT NULL ,[parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[ModifierTYPE] [int] NULL ,[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CondimentPrice] [money] NOT NULL ,[UnitNumber] [int] NOT NULL ,[BusinessDay] [datetime] NULL) ON [PRIMARY]GOCREATE TABLE [parSalesDetail] ([parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[Before] [int] NOT NULL ,[Quantity] [int] NOT NULL ,[After] [int] NOT NULL ,[Promo] [money] NOT NULL ,[PromoBefore] [money] NOT NULL ,[ItemPrice] [money] NOT NULL ,[PromoAfter] [money] NOT NULL ,[POSItem] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[UnitNumber] [int] NOT NULL ,[Depleted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[AmountTenderTime] [datetime] NULL ,[BusinessDay] [datetime] NOT NULL ,CONSTRAINT [PK_parSalesDetail] PRIMARY KEY CLUSTERED([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID]) WITH FILLFACTOR = 70 ON [PRIMARY] ,CONSTRAINT [FK_parSalesDetail_parSalesHdr] FOREIGN KEY([BusinessDay],[UnitNumber],[parSalesHdrID]) REFERENCES [parSalesHdr] ([BusinessDay],[UnitNumber],[parSalesHdrID])) ON [PRIMARY]GO-------------------------------- END CREATE TABLES--------------------------------------------------------------- INSERT INTO TABLES-------------------------------insert into parSalesDetailmodifier values (1,2298561,10917332,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917332,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917332,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917332,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917340,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917340,2,'NOONIN','NO ONIN',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917340,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(4,2298561,10917340,2,'WELL','WELL',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917341,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917341,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917341,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into parSalesDetailvalues(2298561,10917332,0,1,0,.0000,.0000,3.4900,. 0000,'DM',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10917340,0,1,0,.0000,.0000,.2500,.0 000,'JALA',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10917341,0,1,0,.0000,.0000,1.3400,.0000,'MD-DP',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928910,0,1,0,.0000,.0000,.9900,.0000,'2PIE99',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928911,0,1,0,.0000,.0000,.5900,.0000,'DECAF',2,'N','2003-12-0110:09:44.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928912,0,1,0,.0000,.0000,1.6900,.0000,'BOB-BAC',2,'N','2003-12-0110:09:44.000','2003-12-01')insert into parSalesDetailvalues(2298561,10929376,0,1,0,.0000,.0000,.5900,.0000,'COFFEE',2,'N','2003-12-0110:00:44.000','2003-12-01')-------------------------------- END INSERT INTO TABLES-------------------------------
Hi, I'm having problems with this T-SQL: Sorry if this is obvious, I'm very new to this...
Just trying to return the ID if the row exists and if not just return 0. What would I need to turn this into a stored procedure too?
Code Block
declare @Return int
if exists(select id from Computer where Computer_name = 'MYCOMPUTER') begin select @Return = id PRINT @Return end else begin select @Return = 0 PRINT @Return end
I'm getting this error....
Msg 207, Level 16, State 1, Line 5Invalid column name 'id'.Msg 207, Level 16, State 1, Line 10Invalid column name 'id'.
I'm running into a problem on my SQL2000 server. I thought all 3 of the following queries SHOULD return the same results, but the query using the NOT IN does not return any records where the other 2 queries DO.
Is my understanding of NOT IN wrong, or is there something corrupt in my DB?
SELECT * FROM [person] WHERE personid NOT IN (SELECT personid FROM [personOrganization])
SELECT * FROM [person] LEFT OUTER JOIN [personOrganization] ON [Person].[personId] = [personOrganization].[personId] WHERE [personOrganization].personid IS NULL
SELECT * FROM person WHERE NOT EXISTS(SELECT 1 FROM [personOrganization] WHERE [personOrganization].personid=person.personid)
FROM dbo.ExtendedValues(Configurations.ConfigurationId) AS ExtendedValues) AS Expr1 FROM Configurations INNER JOIN
Controllers ON Configurations.ConfigurationId = Controllers.ActiveConfig INNER JOIN
Tanques ON Controllers.ControllerId = Tanques.ControllerId WHERE (Tanques.TanqueId = 1)
Where do I have to enter EXISTS?
Thanks for every hint!!
P.D.: The ExtendedValues Function:
CREATE FUNCTION dbo.ExtendedValues ( @ConfigurationId int = -1 ) RETURNS TABLE AS RETURN SELECT * FROM ConfigurationsTanques WHERE ConfigurationsTanques.ConfigurationId = @ConfigurationId
Hi guys,What is this errorAn attempt to attach an auto-named database for file C:inetpubwwwrootMediaApp_DataLibrary.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I have a textbox1 control where a zip code is entered. What is the most efficient way in C# code behind to see if that zip code exists in tblZipCode? I only need to know if it is there, do not need to know how many occurances.
