|
Question :
i have this table :
T
F X
_________________
F1 X1
F1 X2
F2 X6
F3 X3
. .
. .
. .
. .
Fn Xm
when i want Fs that their X is 'Xi OR Xj OR Xk' i will write
SELECT F FROM TABLE WHERE X IN( Xi , Xj , Xk )
when i want Fs that their X is 'Xi AND Xj AND Xk ' what should i write ?
|
|
|
|
|
Where X = Xi AND
X = Xj AND
X = Xk
To study, study and only to study
|
|
|
|
|
This will actually return nothing, since a single column cannot simultaneously be equal to three different values.
Try this:
Select F, count(distinct x)
from T
where X in (Xi, Xj, Xk)
group by F
having count(distinct x) = 3
Since we are only counting distinct values, and there are three distinct possiblities, a count of 3 means that all are present.
Tony Lewis
XAKTsoft, Inc.
|
|
|
|
|
Thanks
i have use your statement in my article but i got an error with below statement:
Select distinct ID,Name from [View]
where ID not in ( select ID from [View] where TypeID not in(1,3) )
group by ID having count(distinct typeID) = 2
ADO Error :Column [View].Name is invalid in the select list because it is not contained
in either an aggregate function or GROUP BY clause.
|
|
|
|
|
It means just what it says. Any column in the summary query (with a group by clause and aggregate columns like sum, count, etc.) that isn't summarized needs to be included in the group by clause. You need to add [Name] to your group by. I always put the aggregate columns at the end, copy the rest of the selected fields and paste them to the group by. Remember that the group by can't contain field aliases.
Tony Lewis
XAKTsoft, Inc.
|
|
|
|
|
This should work, although I don't know if it's the best way to do it:
SELECT <br />
F<br />
FROM<br />
T T0<br />
WHERE<br />
EXISTS (SELECT 1 FROM T T1 WHERE T1.F = T0.F AND X = Xi)<br />
AND<br />
EXISTS (SELECT 1 FROM T T2 WHERE T2.F = T0.F AND X = Xj)<br />
AND<br />
EXISTS (SELECT 1 FROM T T3 WHERE T3.F = T0.F AND X = Xk)
Of course, all the aliases are arbitrary, just make it easier for me to read.
Hope this helps.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
Plesae try
SELECT F FROM TABLE WHERE X all( Xi , Xj , Xk )
You can read about all, in and any
|
|
|
|
|
 how to handle sql errors with severity less then 10 in C#
.... From russia with love ....
|
|
|
|
|
Here's a link[^] that might help.
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
thanks my canadian bra, i will follow the link
.... From russia with love ....
|
|
|
|
|
frmRussia wrote:
thanks my canadian bra,
I'll assume that should be 'bro' and say 'Glad I could help'. 
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
ok bro. my sql server knowledge is like c# knowdlege --> like english , understand  .
I wrote the link u advise me, but don't really know how to use it my app, if i write in the catch block:
catch(System.Data.SqlClient.SqlError err)
{....}
i got compilation error, "The type caught or thrown must be derived from System.Exception"
.... From russia with love ....
|
|
|
|
|
I think that would be
catch(SqlException se)
{
}
If there is only one error then the exception object will contain the details. If there is more than one error the exception object will contatin the details of only the first error. You then have to look in the errors collection for the other errors.
catch(SqlException se)
{
foreach(SqlError error in se.Errors)
{
}
}
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
i tried it however i never got to catch block with error that severity <=10
.... From russia with love ....
|
|
|
|
|
Hi,
Can anybody tell me how can I determine the inserted row ID (primary key)?
I inserted a new record to the database table but I don't know how to retrive what value was assigned to the primary key (ID). The ID is defined as automatic integer (Access database).
I insert the new record using the SQLExecDirect API method.
Thanks,
Abyss
|
|
|
|
|
Variable @@IDENTITY sets to automatic integer after inserting
e.g
INSERT INTO TableName
VALUES blabla,blabla, ..., ...
DECLARE @myID int
SELECT @myID = @@IDENTITY
.... From russia with love ....
|
|
|
|
|
I'll try it.
Many thanks,
Abyss
|
|
|
|
|
You should really use SCOPE_IDENTITY() to be safe. @@IDENTITY returns the last Identity value no matter what the scope. For example,
You have 2 tables, table1 and table2. table1 has a trigger that when a record is inserted, it inserts a record in table2.
INSERT INTO table1
VALUES ...
--the record you inserted into table1
--created the identity of i.e. 1001
--and it fired the insert trigger
--which inserted identity value of
--i.e. 2002 into table2.
DECLARE @myID int
SELECT @myID = @@IDENTITY
@myID will be equal to 2002 (the value from table2) because @@IDENTITY ignores scope
if you use...
SELECT @myID = SCOPE_IDENTITY()
@myID would be equal to 1001 because the trigger is outside the scope of this query/stored proc/whatever.
Jeff Martin
My Blog
|
|
|
|
|
u absolutly right. my answer was not accurate
.... From russia with love ....
|
|
|
|
|
Hi,
Many thanks for help. I'm a C++ programer and the suggested methods for some reason fail.
I try to get the inserted row's ID (primary key) which was inserted to a table.
I tried the following API calls immediately after insert. I use the same m_hStmn for the insert and the select query.
This call is successfull however when I query the returned value using the SQLFetch and SQLGetData the returned SQLINTEGER is always zero!?
SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT @@IDENTITY AS 'Ident'"), SQL_NTS)
I tried to use the more save SCOPE_IDENTITY() function, however these API calls fail.
The driver returns (for Access table) undefined function.
SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY()"), SQL_NTS)<br />
SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY() As [Ident]"), SQL_NTS)
In this case I got from the driver one parameter missing error code:
SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY"), SQL_NTS)
Can anybody explain how can I correctly get the inserted row's ID? Why the above calls fail  ?
Thanks,
Abyss
|
|
|
|
|
Hi
I think I've got something it can help you
on
www.codeproject.com/cs/database/DBManipulationADONET.asp
Huseyin Altindag
|
|
|
|
|
Well, it is a great idea to use "SELECT MAX(ID) from TableXXX". I suppose that if some record is removed from the table then the given gap is not filled by the new records - the ID is not reused. Always new ID is generated which is greater than the rest. Right?
Thanks,
Abyss
|
|
|
|
|
Hi
Yes but remember always the new ID generated is greater than the last one you insert.
I'll send you(maybe today) a complete application(C#) with SQLServer solution how to do it
Huseyin
|
|
|
|
|
Thanks in advance.
Have you any idea why the original solution fail with "SELECT SCOPE_IDENTITY()" and "SELECT @@IDENTITY AS 'Ident'"? It is on the MSDN - how to obtain the ID of the inserted record. Is it an Access related problem?
Anyway your solution is great I think that it will work in all cases.
Abyss
|
|
|
|
|
Those features are part of Microsoft SQL Server, not Access/Jet.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
