|
First off, my name isn't Colin, but I'll try to help you. I'm not sure why you are only wanting 3 out of that query, but I'll try to explain why you are getting the 3 and 4.
The first where clause TypeID in (1, 3) will return all records with a TypeID of 1 or 3, so the following records are there...
1 | John | 1
1 | John | 3
2 | Mary | 3
3 | Betty | 1
3 | Betty | 3
4 | Joe | 1
The next where clause AND ID NOT IN (SELECT ID FROM [VIEW] WHERE TypeID NOT IN (1, 3))
That will return all records with a TypeID of anything other than 1 or 3, which based on your recordset, will be all TypeIDs of 2. So the AND ID NOT IN [subquery] will result in data that looks like...
3 | Betty | 1
3 | Betty | 3
4 | Joe | 1
Combining those two results in both IDs of 3 and 4. I get the idea you are trying to find all IDs that have a TypeID of 1 AND 3, but not 2. So you don't want 4 in the result set since it does not have a record with TypeID of 3. Is that correct?
If so, here is a query that does that (replace test1 with your View name)...
<br />
SELECT DISTINCT ID<br />
FROM test1 <br />
WHERE TypeID IN (1,3)<br />
and ID IN (SELECT ID FROM test1 WHERE TypeID = 1) <br />
and ID IN (SELECT ID FROM test1 WHERE TypeID = 3)<br />
and ID NOT IN (SELECT ID FROM test1 WHERE TypeID = 2)<br />
If that's not what you are after, you'll have to be a little clearer in your question.
Jeff Martin
My Blog
|
|
|
|
|
This is exact problem :
I have a RDBMS and i want to search and get query . This is a database about document archiving. Each document may have several subject
(as subjectID in DocumentView,a View for filtering , quering and searching ).
In the application the end user can select many subject and gets the documents in two ways:
#1 . Documents that their subjects are X1 OR X2 OR ... OR Xn.
In this case, documents that may have one or more subject in the selected subjects.For example if user selects A , B , G , U as selected subjects
he gets document result set that their subjects are A OR B OR G OR U . I pass the IDs of selcted subjects to stored procedure as nvarchar
( select * from DocumentView where subjectID in ( 1,12,14,18 ) ) that 1,12,14,18 are ID of A,B,G,U respectivly.
#2 . Documents that their subjects are X1 AND X2 AND ... AND Xn.
For example if user selects A , B , G , U as selected subjects ,in this case user must recieve documents that their subjects are A AND B AND G AND U only.
|
|
|
|
|
I have this view :
VIEW
__________________________
ID Name TypeID
__________________________
1 | John | 1
1 | John | 2
1 | John | 3
2 | Mary | 2
2 | Mary | 3
3 | Betty | 1
3 | Betty | 3
4 | Joe | 1
__________________________
when I say :
SELECT DISTINCT ID FROM [VIEW]WHERE TypeID IN (1,3)AND ID NOT IN (SELECT ID FROM [VIEW]WHERE TypeID NOT IN (1,3))
I get :
3 , 4
But I Want to have only :
3
I dont know what should i do !
|
|
|
|
|
Put aliases around the view names to make sure the query plan gets constructed properly.
SELECT DISTINCT ID
FROM [VIEW] x
WHERE x.TypeID IN (1,3)
AND x.ID NOT IN ( SELECT ID FROM [VIEW] y WHERE y.TypeID NOT IN (1,3) )
|
|
|
|
|
Any one pls give the definition fo sql stored procedure
or what r stored procedure in sql
what r triggers in sql
and finally
difference betn sql triggers and stored procedures
difference betn function in sql and stored procedure
Patel Neelesh A
|
|
|
|
|
A stored procedure (SP) is just a procedure written in SQL. It takes input parameters and can have a return value, but can also return result sets.
A user defined function (UDF) is also like a procedure but written in SQL, however a UDF is more strict. For instance it can only use deterministic functions. UDFs can be used in other queries in the place of values or tables.
A trigger is a special sort of stored procedure that is run automatically when a specific action occurs, for instance a SELECT, INSERT, UPDATE or DELETE on a table. The trigger can perform additional actions depending on what triggered it.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Sir is it possile for u to give more details on this topic with some example and also advantages of each of
If possible pls give a simple example of triggers pls
Patel Neelesh A
|
|
|
|
|
Colin, I'm glad that you're very helpful but this request sounds suspiciously like someone's homework.
IMHO giving him the short explanation is ok but any more and you might as well be doing his homework for him  and that won't really help him further down the track.
Man, I was never this lazy back in my school days.
Edbert
|
|
|
|
|
Hi everyone,
I've wondered across a peculiar issue with ADO (although something else could be the culprit, but through my investigation, I believe it to be ADO).
I have written a service that uses ADO. When running the service as a console app, everything works great. However, when the service is run 'as a service' in the SCM, I find a problem with the connection.
Basically, this is what I've found to happen:
1. I issue a connect() call, and it returns S_OK.
2. Immediately after that, I execute a simple query, say "select count(*) from table1". At this point, when running as a normal app, the query executes and all is well. However, as a service, the query fails. Upon further inspection, doing a check on whether the connection is actually "connected", I find that it is infact disconnected.
Any ideas? I've been thrashing my brains with this problem for a few hours now. Maybe someone can shed some light on this?
I have tried changing the account under which the service runs. That didn't work. I tried testing different data providers. Same problem. Anything else I can try?
Thanks,
Peter
controlSHIFT
[Glossary Manager] [AfterThought Backup Lite]
All good things were meant to be improved
|
|
|
|
|
Peter Mares wrote:
I have tried changing the account under which the service runs. That didn't work.
hmmm...... That was my first thought. I have written a few services that update different data sources (dB2, MS SQL, Access) with ADO and ADO.net and not had this issue.
Just as an idea, is it possible that you are catching an exception somewhere in your code that is not getting propigated back to the caller?
Hey don't worry, I can handle it. I took something. I can see things no one else can see. Why are you dressed like that?
- Jack Burton
|
|
|
|
|
Hey, thanks for the reply.
I found the problem. When running under the SCM as a service, the SCM obviously takes the "entry point" nominated in the dispatch table that is passed into StartServiceCtrlDispatcher( ) and puts it into another thread. Since I was calling CoInitialize BEFORE this, COM never got initialized, or so it seemed. Simply putting CoInitialize into the entry point function sorted me out.
As an aside, I am slightly curious why, before the fix, calls the ADO Connection::Open( ) function SUCCEEDED and the CreateInstance( ) function returned what seems to be a valid interface pointer to the Connection object - weird.
Anyhow, atleast its sorted out 
Thanks,
Peter
controlSHIFT
[Glossary Manager] [AfterThought Backup Lite]
All good things were meant to be improved
|
|
|
|
|
How do I query Access Date field, My query returns no records
This is my query
"Select * from Score where testDate = 3/10/2005"
|
|
|
|
|
|
AFAIK in Access you use # in between the date, e.g. #3/10/2005#
Use the ISO format that Colin mentioned though (#2005-10-03#).
Edbert
|
|
|
|
|
Edbert P. wrote:
AFAIK in Access you use # in between the date, e.g. #3/10/2005#
I try this but still no result
Select * from
Score where testdate = #2005-03-10#
And there is a record with that date
|
|
|
|
|
Perhaps the date you supplied is in mm/dd/yyyy (U.S.) format instead of dd/mm/yyyy format.
Try testing with several dates and combination of dates and see which one gives you a result.
Edbert
|
|
|
|
|
I am new to VB6 and i have developed a small application. i have used ado component version 2.5 and when i deploy my application by pdwizard, a messege appears that some ocx components are out of date. and another message appears that dependancy information for msado25.tlb not found. but it builds the setup.
when i install it on target mechine it gives message "Error Registering msado25.tlb" if i ignore it the queries i used in database file does not work and the message appears "Class does not support the automation..."
Ali Da Malang
|
|
|
|
|
This is a bug in the P&D Wizard. It finds the msado25.tlb in your project as a dependency and includes it in the build, but the Setup program cannot register type libraries. Also, even if the type library registered correctly, you need other components for ADO to work correctly.
The only way to correctly install ADO services on a target machine is to use the MDAC installation.
To fix all your problems you need to do two things.
1) When you build your package, manually remove the msado25.tlb from this list of included files. This will prevent the user from seeing the "Error registering msado25.tlb" message.
2) Make sure the MDAC is included in the build. This will correctly install all the ADO services.
Robert
|
|
|
|
|
can any one tell what should one prefer a dataset or a datareader
what is diff betn the two
Patel Neelesh A
|
|
|
|
|
I am guessing you mean DataAdapter rather than DataSet (as a DataSet does not communicate with the database, while a DataReader does)
A DataReader allows you to get data from the database in a fast forward only fashion. This means that you can start processing the records before all are retrieved from the database. It is useful for situations where you are reading data into your domain objects and don't want/need any extra copies of the data in your system.
A DataAdapter reads all the records into a DataSet first meaning that there can be a slight delay and a larger memory overhead. It is useful for data binding to controls and prototyping.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
where date > @Date1 and date < @Date2 or using between or there's a faster solution?
<italic>Work hard, Work effectively and a bit of luck is the key to success.
|
|
|
|
|
Try it and see. I'd guess that between internally does > < and is probably slower for the extra step, but only by a miniscule amount.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Hi All,
I was wondering if I could get some advice on this. What Im trying to do is view a stored procedure in an asp page. I dont know much about it and was wondering is it like displaying records in a table? Any help/links would be greatly appreciated. Thanks in advance
Nic
|
|
|
|
|
Assuming you are talking about SQL Server 2000 (You didn't mention a database product - so the rest of this answer may not apply) you can perform a query like this:
select * from syscomments
inner join sysobjects on sysobjects.id = syscomments.id
where xtype='P'
Which will return all of the stored procedures and the their uncompiled source.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Or using INFORMATION_SCHEMA:
SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.Routines
WHERE ROUTINE_CATALOG = 'MyDatabaseName'
AND ROUTINE_NAME = 'MyProcName'
MS SQL Server will return NULL if the procedure has been encrypted.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
