The problem is a simple full outer join ends up ignoring EmpID 3 who has no Calls in t1, but I still want that row displayed in the results. Any ideas? TIA
Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...
I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.
By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.
SELECT itemNumber FROM itemNumberSet WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable) ORDER BY itemNumber
SELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullNameFROM Employee e INNER JOIN EmployeeEval ev ON ev.PeriodID = @Period WHERE (ev.Approved = 0) AND (e.DeptID = @deptID)GOI want to select everyone from the employee table in a dept (determined by DDL) who has either a) had a review and not been approved yet - or b) has not had a review yet ---- all employees are in the employee table -- and all reviews are placed in the employeeeval table.
So I've been working on this project and I've finally gotten into the program stages. However, I've realised some things that I need to change to some of my stored procedures so that they work the way I want them to inside the program. Anyways I have this stored procedure called "Delete_Minors" and what it originally did was delete any class found in this table called MinorRequiredClasses that matched that minor and was not considered complete. I realised that this would be a bad decision to delete classes in a minor and not consider any other majors/minors a user might have that also require those classes...SO I thought I might be able to come up with a query but it doesn't seem to work. Below are the tables the query/stored procedure deals with as well as the old procedure and the new one (that I attempted to work the way i wanted it to).
Code Snippet
DECLARE @studid int DECLARE @minorid varchar(50)
SET @studid = 0 SET @minorid = 'Computer Science'
IF (SELECT COUNT(*) FROM Student_Minors sMinors WHERE sMinors.MinorID = @minorid AND sMinors.StudentID = @studid) > 0 BEGIN DELETE FROM Student_Classes WHERE StudentID = @studid AND Completed = 0 AND ClassID IN (SELECT minReqC.ClassID FROM MinorRequiredClasses minReqC WHERE minReqC.MinorID = @minorid) AND ClassID NOT IN (SELECT majReqC.ClassID FROM MajorRequiredClasses majReqC WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID FROM Student_Majors sMajors WHERE sMajors.StudentID = @studid))
DELETE FROM Student_Minors WHERE StudentID = @studid AND MinorID = @minorid END
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MinorRequiredClasses]( [MinorClassID] [int] IDENTITY(0,1) NOT NULL, [MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED ( [MinorClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID] GO ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID]) REFERENCES [dbo].[Minors] ([MinorID]) GO ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MinorRequiredClasses]( [MinorClassID] [int] IDENTITY(0,1) NOT NULL, [MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED ( [MinorClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID] GO ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID]) REFERENCES [dbo].[Minors] ([MinorID]) GO ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorRequiredClasses] Script Date: 04/07/2008 22:50:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MajorRequiredClasses]( [MajorClassID] [int] IDENTITY(0,1) NOT NULL, [MajorDisciplineID] [int] NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorRequiredClasses] PRIMARY KEY CLUSTERED ( [MajorClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_ClassID] GO ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID]) REFERENCES [dbo].[MajorDisciplines] ([MajorDisciplineID]) GO ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID]
OLD QUERY DECLARE @studid int DECLARE @minorid varchar(50)
SET @studid = 0 SET @minorid = "Computer Science" DELETE FROM Student_Classes WHERE StudentID = @studid AND Completed = 0 AND ClassID IN (SELECT minReqC.ClassID FROM MinorRequiredClasses minReqC WHERE minReqC.MinorID = @minorid)
NEW QUERY DECLARE @studid int DECLARE @minorid varchar(50)
SET @studid = 0 SET @minorid = 'Computer Science' DELETE FROM Student_Classes WHERE StudentID = @studid AND Completed = 0 AND ClassID IN (SELECT minReqC.ClassID FROM MinorRequiredClasses minReqC WHERE minReqC.MinorID = @minorid) AND ClassID NOT IN (SELECT majReqC.ClassID FROM MajorRequiredClasses majReqC WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID FROM Student_Majors sMajors WHERE sMajors.StudentID = @studid)) Let me know if you need more information...Hope someone can help!
I have a hunch this one is going to be a resounding "no", but I thought I'd try anyways.
I have a report that uses a user defined date range many, many times throughout the datasource. Ideally, I would like to pass a query declaring and setting variables and let sql server (2000) sort out the dirty work. Essentially I'm working on something that would look like this:
DECLARE @sDate AS DATETIME DECLARE @eDate AS DATETIME
SET @sDate = 'this string gets constructed during the On Open event of a report SET @eDate = 'same thing here
SELECT lotsOfStuff, (SELECT oneOfManySubSelects FROM t2 WHERE t2.field BETWEEN @sDate AND @eDate) FROM somewhere WHERE somefield BETWEEN @sDate AND @eDate
I have some five subselects that are dependent on this daterange. I can construct the entire string purely in VB, but it's messy and rather tedious. Ideally I'd like to set the variable ONCE at runtime and be done with it. This way, I keep a full record source that calls @sDate and @eDate. Then I simply set the variables and insert them before the query.
The problem is Access doesn't seem to know how to pass the query without trying to parse the variables itself. So it gets mad that @sDate and @eDate haven't been defined for each occurance. I'm looking for a way to make access ignore the fact that there are variables in the query, and pass it as-is to the sql server.
I assume that MS has a directive never to change the format of SSIS raw files...
However, what I'd like to know is that when I'm planning long-term systems where I've got backups of data (staging, logging, whatever) using raw files, can I be assured that future versions of SSIS will be able to read those raw files?
I assume a certain level of backwards compatibility, however, I'm just curious if I should think about building processes into my projects that would factor that in and rebuild raw files everytime a new/major release of SSIS comes out.
So, Jimmy G helped me out with it in showing a little bit how to do it. SqlCommand command = new SqlCommand(, object>) SqlParameter param = new SqlParameter(); param.ParameterName = "@return"; param.Direction = ParameterDirection.ReturnValue; command.Parameters.Add(param);
command.ExecuteNonQuery();
//get the return value int val = int.Parse(command.Parameters[0].ToString()); Where I get lost is in the declaring of a new sqlcommand and sqlparameter. Can you please spell out where to use this and if I need to change my SQLdataSource. I currently was trying to use it in the OnClick of a button. What I had did the following Protected Sub CreateIssue_Click(ByVal sender As Object, ByVal e As System.EventArgs) dim returnValue as integer 'how do I get a return value from the stored procedure executed in 'insertissue.insert() here to a variable? InsertIssue.Insert() Response.Redirect("/addarticletoissue") End Sub
again, thank you for your help and patience with such a beginner =)
I have a production 60GB database set to Full Recovery and every 15 minutes I am log shipping to a Stand by Server .
During the production hours there are no problems but at night when I run DBCC DBREINDEX, the log grows to 22GB and because of this I have a problem sending this over the network to the stand by server.
I tried changing the recovery model to Bulk_Logged but the there is no difference in log file backup size.
It is my understanding that Views cannot have parameters. Also thatstored procedures can not be queried. My problem is this:I want to select the rows that match a certain parameter.From that I want to select the most current 20 rows (there is a datefield).From that I want to select the lowest 10 rows based on a numericfield.Finally I want that to be input to a report and some calculations.What this basically is the selection for USGA Golf Handicap Index. Itis the most current 20 rounds of golf by a golfer, then the best 10 ofthose 20 and then finally the calculation.Any help would be appreciated.
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
...when I started this endeavor. I have a previously developed Lotus Notes App. The idea was simple; as I am sefl taught on Lotus Script, I figured I'd be able to stumble my way through VB.Well, it started OK. I used VB Express to get familiar with the stuff, but decided to go with a full version of VS 2005 and try and get this thing properly developed as a web app. I purchased several reference books etc., and have become relatively familiar with the forums here.First issue I have is that I simply want to use code to update or add records to an SQL DB. I know about datagridview etc., but I want to update the DB using forms, not the tabular view those controls provide. I thought it would be relatively straight forward, but found my ignorance runs deeper than I thought. When I tried to do so I am finding I am not really clear on where to make declarations etc. in the web app. If anyone could point me in the right direction that would be great. The issue with searching these forums is most posts deal with datagridview or something similar.I have spent a ton of time trying to find relevant posts or articles, but have had no luck yet.Again, all Ireally need is a nudge in the right direction. I am more than willing to plod through reference materials or articles/posts to find what I need to know, I just can't find where to even start on the info I need. Regards, Joe
Hi I have a problem with my sql WHERE query, if i manually type ([Area] = 'The First Area') then it is okay but if i try and pass the variable 'The First Area' using the
([Area] = @Area) it doesnt work. ALTER PROCEDURE dbo.StoredProcedure1(@oby nvarchar,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT)ASSELECT * FROM(SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) Please Help I know it must be something simple as the sql works but not when i pass the variable.... Thanks In Advance
Output: First row: initial values of the fields Second row: average of the same fields
Please help me...
select * from ( select HEM_LOKOSIT, HEM_NNS from LPMS.HEMOGRAMS where HEM_PATIENT_ID = 33 union select AVG(HEM_LOKOSIT), AVG(HEM_NNS) from LPMS.HEMOGRAMS where HEM_PATIENT_ID = 33) order by HEM_LOKOSIT desc nulls last;
I'm relatively new to SQL7 but I did use 6.5 a fair bit. I'm trying to test the restore of the Transaction log backup and having a bit of difficulty. The idea is that I make a complete database backup at 1am backup the transaction log every 30 minutes between 7am and 7pm. I need to be able to restore the database to a known state between 7am and 7pm with a max data loss of 30 minutes.
What I am trying to achieve is (as a test):
1)Create a small test database with a test table 2)Add some data to the test table 3)Back up the transaction log 4)Restore the transaction log to 'undo' the data added in step 2
Should be simple I think !!! The problem I am encountering is that in step 4 it won't let me restore only the transaction log (a tick automatically appears in the database backup as well). Bah !
Can someone please tell me what simple steps are required to get this to work. I need specifically on what options to chose during the backup and restore processes.
Hi, I have a table with two columns. I need to find distinct value of col1 and the correspondin repeated value of col2 for that col1 value with comma seperated list. Is there any function for this in MS SQL? I need somethgn like a 1,2,3 b 4,5 c 7 d 5,55,5
I can do that with creating 2 cursors but looking for some easy way around.
DELETE FROM #RptDetails WHERE StructureType <> @StructureType AND #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
But it didnt delete the structure types i changed to : DELETE FROM #RptDetails WHERE StructureType <> @StructureType --AND DELETE FROM #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
and it did, how do i format the 2nd sql into 1 statement and what was i doing wrong?
I think it's simple, but I can't get it to work.In English its: find records in TableA where the field [Field1] hasmore than one unique value in Field2sample records in TableAField1 Field22241 123452241 123452242 123452242 99856desired return (2 records)2242 123452242 99856thank you for your helpPaul
I have a form that after being filled out has its contents written to adatabase and then goes to confirmation .asp page. I want the confirmationpage to be personalized but I am not sure how to pass the "name" parameterto the confirmation.asp page. I know I am going to kick myself whrn I seethis.Thanks, Houston
I'm trying to find out if a particular column in a table has more than 10 digits. for example: the TN column. How can I write this query? The data type for this column is bigint. Also, would it be better to change this data type to char(10) instead as it's used primarily for phone # lookups? Thanks in advance!
Hi, Im trying to create a simple search page to return a list of products from a table in a sql database. Id like to be able to search a product description column in a table I have called products using the contents of a text box. I was wondering how i can go about getting the search to check for all words the user types in rather than just a single word or an exact phrase. Im currently using the following sql query SELECT [product_title], [product_description] FROM [products] WHERE ([product_description] LIKE '%' + @product_search + '%') this works fine for single words and exact phrases but if i had product called 'fred w bloggs' and i enter 'fred bloggs' it will not return anything. Please could anyone suggest how i shoud go about this? Im not sure if my web hosting company will enable full text search or will this be required? Thanks for any help! pete_ (very new to asp.net!)
hay friends scene is that i wana read single multiple rows of a single column from a sql database and then want to shows those values in text box,,, so plz tell me ho to do it. By using data set ,,data table or what to use for this and how.... wll be waiting for ur coordination