|
select *
from problems a
, tblnotes b
where a.id=b.id
and b.adddate =
(select max(adddate)
from tblnotes x
where x.id = a.id)
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
I'm doing an web project about virtual stock exchange. With me that project is very important and i'm really worry about my database. I wonder is there anybody interested in my project 'n can help me improving my database better or somewhere can check my database? If you can, plz email to me : duc.to.ho@gmail.com
Thank you!
|
|
|
|
|
[WebMethod]
public DataSet GetEmployees()
{
.
.
.
}
or
[WebMethod]
public []Employee GetEmployees()
{
.
.
.
}
which Employee is business object and using dataReader is filled.
|
|
|
|
|
WDI wrote:
public []Employee GetEmployees()
you mean :
public Employee[] GetEmployees()
I think !!
I prefer the second method..especially when you use datareader to get the data.
|
|
|
|
|
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
__________________________
when I say :
select distinct ID from VIEW where TypeID in ( 1 , 3 )
I get :
1 , 2 , 3
But I Want to have only :
3
I dont know what should i do !
|
|
|
|
|
That is the correct result for the query you supplied. IN(...) means that the value can be any one of the values in the list.
First an explanation of why you are seeing the results you get.
Breaking this query down into smaller parts:
SELECT * FROM VIEW WHERE TypeID IN (1,3) will return
__________________________
ID Name TypeID
__________________________
1 | John | 1
1 | John | 3
2 | Mary | 3
3 | Betty | 1
3 | Betty | 3
__________________________ SELECT ID FROM... will return
__________________________
ID
__________________________
1
1
2
3
3
__________________________ And so SELECT DISTINCT ID FROM... will return
__________________________
ID
__________________________
1
2
3
__________________________
So, if you only want to get the IDs where there is a TypeID of 1 and 3.
The SQL you are looking for is:
SELECT DISTINCT ID FROM [VIEW]
WHERE TypeID IN (1,3)
AND ID NOT IN (SELECT ID FROM [VIEW]
WHERE TypeID NOT IN (1,3))
What this does is get a list of the IDs as you did before, but it also subtracts the IDs that have values NOT IN the list of TypeIDs that you specify
SELECT ID FROM [VIEW]
WHERE TypeID NOT IN (1,3)
returns a list of IDs that you are not interested in.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I altered VIEW as below and wrote your statement
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 | Michael | 1
_________________________
i get 3,4 ,but i want 3 only.
what should i do
|
|
|
|
|
Okay - I've added some extra SQL to the statement I wrote previously. I couldn't think of a very elegant solution to this so it is actually in two parts. First I create and populate a temporary table (actually a table variable) with similar results to the query that you have already. (I remove the distinct and add the TypeID column to the output). Secondly, I take the results of the first part and perform a self-join (in other words I join the table to itself) so that I can find only those that have all the relevant TypeIDs rather than just any of the typeIDs.
-- Create and populate the table variable
DECLARE @ids TABLE(ID int, TypeID int)
INSERT INTO @ids
SELECT DISTINCT ID, TypeID FROM [VIEW]
WHERE TypeID IN (1,3)
AND ID NOT IN (SELECT ID FROM [VIEW]
WHERE TypeID NOT IN (1,3))
-- The final result comes from the table variable.
SELECT DISTINCT a.ID
FROM @ids AS a
INNER JOIN @ids AS b on a.id = b.id -- self-join
WHERE a.TypeID = 1 AND b.TypeID = 3 -- Ensure that the ID has both desired typeIDs
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I have a wordier solution for you. It may be easier to understand and maintain. Only testing will tell you which is faster for your dataset. 2 tables scans verse 2 correlated sub queries/row. Mine might be faster if you have an index like (ID,TypeID) on the table. Then again, it might really suck  .
SELECT
DISTINCT v.ID
FROM
View v
WHERE
EXISTS (SELECT * FROM View where ID = v.ID AND TypeId = 1) AND
EXISTS (SELECT * FROM View where ID = v.ID AND TypeId = 3)
|
|
|
|
|
3rd solution. Let the server do straight set work. This might be the quickest on large datasets.
SELECT a.ID
FROM
(SELECT ID FROM View WHERE TypeId = 1 GROUP BY ID) a
INNER JOIN
(SELECT ID FROM View WHERE TypeId = 3 GROUP BY ID) b
ON (a.ID = b.ID)
|
|
|
|
|
The first solution is true but second and third ,both of theme are incorrect.
|
|
|
|
|
Sorry, I didn't read your question close enough.
|
|
|
|
|
How to call for update for one field e.g. 'ID' if we have 2 different databases that having different table name but same field name which is 'ID' ? If we want to update for field ID in table 1 database 1, then it should update for field ID table 2 database 2 as well..need help on this!
mijan
|
|
|
|
|
Hi,
you can update the table in a different database by referring it as <database>.<owner>.<table>
For eg.,
update a
set a.fielda = b.fieldb
from test1.dbo.t1 a, t2 b
where a.fielda = b.fieldb
|
|
|
|
|
I have no idea where to start. I have a table names Pages. A field PageID, PageNumber, etc.. (those only two matters). I delete some page and need to take the one at the end of the book(?) and bring it to the place where the page where deleted (Change the PageNumber). I need to do this in a stored proc of sql function. I don't know where to start ! 
sebastien.lachance.blogspot.com
|
|
|
|
|
Here is something to get you started. It really needs to be wrapped in a transaction with a 'SERIALIZABLE' isolation level. Brutal if you have a lot of users.
DECLARE PROCEDURE DeletePage
(
DECLARE @PageID INTEGER
)
AS
DECLARE @MaxPageNumber INTEGER
SELECT
@MaxPageNumber = MAX(PageNumber)
FROM
Pages
DECLARE @PageNumber INTEGER
SELECT
@PageNumber = PageNumber
FROM
Pages
WHERE
PageID = @PageID
IF @PageNumber IS NULL
BEGIN
-- Deal with Bad PageId
RAISERROR('Bad PageID',16,1)
RETURN 1
END
DELETE
FROM
Pages
WHERE
PageID = @PageID
IF @MaxPageNumber <> @PageNumber
BEGIN
UPDATE
Pages
SET
PageNumber = @PageNumber
WHERE
PageNumber = @MaxPageNumber
END
RETURN 0
|
|
|
|
|
i want to have a oledb connection in my module so that all the forms in my project can use it
how to build it
|
|
|
|
|
This is not a good approach. First of all, you would have to write extra code to make sure the connection is closed before the application terminates. Secondly, you cannot create the connection WithEvents so you cannot trap errors such as the connection being lost.
You should use a class instead. That way you can trap events and most important, make sure to close the connection by addding one line of code in the class destructor.
Robert
|
|
|
|
|
Happy Saint Patrick's Day everyone. I'm stuck here at work, (wish i was watching basketball) but on to my problem. I need to run a query that will return a list of applications. I think I need to do a left join(?) as not all apps will have a person listed. I still need to display the rest of the info though, even if there isn't a person for it. Here is an example what I have so far:
(BTW: I'm using oracle 8, and sql is not my specialty "obviously")
Select d.application_name,
f.first_name,
f.last_name,
d.servlet_context,
d.after_hours_contact,
d.context_description,
d.database_access,
d.test_link,
d.notes,
e.description,
f.user_id,
d.app_id,
a.team_name
from
web_team a,
web_developers b,
web_app_resp_dev c,
web_application_info d,
web_app_resources e,
user_profile f,
web_team_lead g
where
d.team_id = a.team_id and
d.app_id = c.app_id and
a.team_id = 1
and
f.user_id = c.dev_id and
(f.user_id = b.dev_id or
f.user_id = g.lead_id) and
d.app_id = e.app_id
order by d.application_name, e.description, f.last_name,
f.first_name
Brian Van Beek
|
|
|
|
|
Ooops...I forgot to add a little more info, i need all apps in table "d" even if they don't have a corresponding person in table "c"
Brian Van Beek
|
|
|
|
|
change d.app_id = c.app_id and
to d.app_id = c.app_id (+) and
|
|
|
|
|
Hi..,
Does any body have know about querys of take backup and restore of db with paraments like user name, password, connection?
Does any body no how export and import data to Access or any other db format with the same paraments as above?
Please help i am using C#.Net in programming.
|
|
|
|
|
WaqasButt wrote:
Does any body have know about querys of take backup and restore of db with paraments like user name, password, connection?
I am not entirely sure I understand your query - but I'll do my best.
I am guessing that you want to backup and restore from a C# application and you need to know what SqlConnection you need in order to perform that action.
According to the SQL Server books on-line to take a backup you need be a member of the sysadmin fixed server role or the db_owner and db_backupoperator fixed database roles for the database you are backing up.
Therefore the username that you use in the connection must be a user that is part of one of the above roles. For example "sa" is a member of the sysadmin role
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
hi ,
I am new ASP.NET Programmer and I am trying to access the data in the oracle database I wanted to access using Managed Provider can any one of you suggest what are the classes for this.IS their any other way to access the data from oracle database.
Thanking you.
Satish
|
|
|
|
|
How do you do insert on identity column with DTS?
Function Main()
DTSDestination("UIN") = ???IDENTITY???
DTSDestination("address") = DTSSource("Address")
Main = DTSTransformStat_OK
End Function
Thanks in advance!
Norman Fung
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
