|
Hi,
I have training Database which contains 3 tables, STUDENTS,COURSES and STUDENT_COURSE which contains the F.k for the Students and Courses tables. The STUDENT_COURSE Contains StudentID,CourseID as Primary Key
I need to write Stored Procedure that Return All Students attends 'MCSE' and 'MCSD' Courses.
The following are The Attributes of all 3 tables
1- Students
A. StudentID P.K
B. StudentName
C. Mobile
D.Address
2- Courses
A. CourseID P.k
B. CourseName
3- Student_Course
A.StudentID P.k, F.k
B.CourseID P.k, F.k
regards
|
|
|
|
|
select students.*,Courses.*,Student_course.* <br />
from students,Courses,Student_Course<br />
where Student_Course.CourseID = Courses.CourseID and <br />
(Courses.CourseName = 'MCSE' or Courses.CourseName='MCSD')
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
When i tried to update the contents of a table in my database i am getting the "Could not save; currently locked by another user" exception.
I tried closing the connection other places where ever it is used.
But still the same problem is coming? what should i do? Please help me?
|
|
|
|
|
Hi,
on the pc hosting my sqlserver I have a file, i.e C:\temp\myimage.jpg.
On my sql I have a table like this:
Images{data (verbinary(max)};
I need my sql server to load c:\temp\myimage.jpg and store it in table Images
Somebody knows a way to do this?
Life is not short... the problem is only how you organize yourself
|
|
|
|
|
Dear All,
Iam new to Sql server 2005 reporting services.I have a small problem in that.
I have a column which consist of the flowing values
1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
1.3
1.3.1
1.3.2
2
2.1
2.1.1
2.1.2
2.2
2.2.1
2.2.2
2.3
3
3.1
3.12
3.13
3.2
3.21
3.22
So I want to make toggle like this.
Means initially it should display
1,2,3 in a collapsid mode
when i click one it should display
1.1,1.2,1.3 when i click 1.1
then it should display 1.1.1,1.1.2
Like this. Please help me on this
1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
1.3
1.3.1
1.3.2
2
2.1
2.1.1
2.1.2
2.2
2.2.1
2.2.2
2.3
2.3.1
2.3.2
3
3.1
3.1.1
3.1.2
3.2
3.2.1
3.2.2
Regard's
Veeresh
i want to join this group
|
|
|
|
|
Double post. Has one just below.
|
|
|
|
|
Dear All,
Iam new to Sql server 2005 reporting services.I have a small problem in that.
I have a column which consist of the flowing values
1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
1.3
1.3.1
1.3.2
2
2.1
2.1.1
2.1.2
2.2
2.2.1
2.2.2
2.3
3
3.1
3.12
3.13
3.2
3.21
3.22
So I want to make dril down like this please help me on this..
1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
1.3
1.3.1
1.3.2
2
2.1
2.1.1
2.1.2
2.2
2.2.1
2.2.2
2.3
2.3.1
2.3.2
3
3.1
3.1.1
3.1.2
3.2
3.2.1
3.2.2
i want to join this group
|
|
|
|
|
I cannot get more than two columns using this simple SELECT query.
m_FDRecordset = new FDRecordset();
m_FDRecordset->Open(AFX_DB_USE_DEFAULT_TYPE,
"SELECT [Column1],[Column2],[Column3] FROM [Table] WHERE [Column1] LIKE '" + text + "%'", CRecordset::readOnly );
All I get is "Error retrieving record".
Being a total greenhorn in SQL I am lost.
Could someone give me a hint what is missing.
The query works fine with just two columns selections.
I am using Access as a database.
Here is an addendum to my dilema.
I am getting this error message in my trace.
Invalid character value for cast specification on column number 4
I have copied working column #2 to columns 3 and 4.
They are "text" in Access database.
As soon as I add data to these columns I get this error.
If my columns data are same why I get this error?
PS I am stuck with Access.
Thanks for reading.
Vaclav
The "problem" was in Access column data type - it does not accept
"3.5 MHz" either as text or number. The SELECT than fails.
But "1A" is accepted as text - no problem.
Live and learn!
modified on Wednesday, May 21, 2008 10:54 AM
|
|
|
|
|
Try using Select * from table and inspect the results to make sure the cols are named correctly.
Recommendation - move from Access to SQL Expres, URGENTLY
See this lounge discussion - save yourself some nightmares!
clickety[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No good, same error message.
What is puzzling is that it returns one or two columns correctly.
Vaclav
|
|
|
|
|
Dear all,
I have a 'Games' table in testxyz db following:
create database testxyz
go
use testxyz
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Games]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Games]
GO
CREATE TABLE [dbo].[Games] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Point] [int] NULL ,
[Dates] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Games] ADD
CONSTRAINT [DF_Users_Dates] DEFAULT (getdate()) FOR [Dates],
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
GO
--Insert data:
DELETE FROM [Games]
GO
SET IDENTITY_INSERT[Games] ON
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(1,'C.John',10,'5/19/2008 7:20:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(2,'C.John',12,'5/18/2008 7:10:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(3,'C.John',6,'5/17/2008 7:22:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(4,'C.John',20,'5/19/2008 7:45:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(5,'A.Jerry',4,'5/19/2008 7:28:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(6,'A.Jerry',8,'5/19/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(7,'A.Jerry',10,'4/1/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(8,'A.Jerry',67,'4/1/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(9,'Nancy',50,'5/11/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(10,'Nancy',10,'5/21/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(11,'Nancy',78,'5/25/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(12,'A.Jerry',78,'5/26/2008 7:19:59 PM')
SET IDENTITY_INSERT[Games] OFF
GO
Now, I want to get rows which have maximum 'Point' and minimum 'Dates'.
If Point of user is the same. it gets a row with minimum 'Dates' condition. Note that result will sort by Point 'DESC'. UserName do not repeat in the result.
In data above. Expected result is:
UserID | UserName | Point | Dates
11 | Nancy |78 | 5/25/2008 7:19:59 PM
12 | A.Jerry |78 | 5/26/2008 7:19:59 PM
4 | C.John |20 | 5/19/2008 7:45:59 PM
Please help me!
Thanks a lot
mangrovecm
(-,-)am from VietNamese.
modified on Tuesday, May 20, 2008 9:13 PM
|
|
|
|
|
Their are multiple solutions for that. You could try:
select g.*
from games g
inner join (
select point=max(point), date=min(dates)
from games
) d on g.point = d.point or g.dates = d.date
order by g.point desc
or:
select *
from games
where point = (select max(point) from games)
or dates = (select min(dates) from games)
order by point desc
Both give the same results.
Wout Louwers
|
|
|
|
|
Dear WoutL,
The result did not expected. Because 'A.Jerry' UserName appear in 3 rows.
The result must be not repeat the same UserName. That mean the result will be:
row 1: 11 | Nancy |78 | 5/25/2008 7:19:59 PM
row 2 12 | A.Jerry |78 | 5/26/2008 7:19:59 PM
row 3: | C.John |20 | 5/19/2008 7:45:59 PM
Could you help me!
Thanks a lot
mangrovecm
(-,-)am from VietNamese.
|
|
|
|
|
Without the UserID it would be simple:
select UserName, points=max(point), date=min(dates)
from games
Group by UserName But the problem is thath a singel user has more than one userid. So which userid should be returned? I think it is better to redesign the database and split the games table in a user and games table.
Wout Louwers
|
|
|
|
|
Hello,
I have the following query working well, however; I need to augment it and what seemed simple has really stumoed me. Maybe some of you can guide me in the right direction.
use db1
select distinct v.userid, v.firstname, v.lastname, c.[name]
as 'credential field', q.address, value from import3
inner join vulture v on v.userid = external
inner join credentialfielddef c on c.id = fieldid
inner join vulture_commo o on o.vultureid = v.id
inner join Commo q on q.id = o.vultureid where q.commotypeid like '4'
order by userid
Results look like this:
UserId Firstname Lastname Crednetial Field Value
GUID Jim Roberts Field1 Test1
GUID Jim Roberts Field2 Test2
GUID Jim Roberts Field3 Test3
GUID Jim Roberts Field4 Test4
Desired Result set:
UserId Firstname Lastname Field1 Field2 field3 field4
GUID Jim Roberts Test1 Test2 Test3 Test4
The schema is nightmare and it took me a while just to get where I am now but I am stuck and need a little insight as to the best approach. I have tried using varibles and considered case statements but I am truely lost.
Any help would be appreciated on the best method.
Regards,
Hulicat
|
|
|
|
|
How are stored your data on table, can you post here data on your table?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I was able to accomplish the desired result via CASE statements.
Thanks,
Dennis
Regards,
Hulicat
|
|
|
|
|
Coool...
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I need your opinions about using transactions with Sql Server. I need to insert data to three tables and if anything fails I need to delete all inserted data. So I'm going to use transactions but I don't know where I should use it. I have three stored procedures for the job and I use typed DataSet in my application.
First option is use SqlTransaction and call three sproc in scope using QueriesTableAdapter. The problem is I'm not sure if this can rollback if anything fails.
Second option is don't do anything in application. Call a fourth sproc with all data and use transaction in that sproc and call all three sprocs using exec etc. Also the problem with this is the same, I don't know if it'll rollback using it like this.
What do you think? I appreciate any opinions.
|
|
|
|
|
Both will work fine, which to use is mainly a matter of oppinion and preference.
|
|
|
|
|
|
Are they still same if I use System.Transactions instead of SqlTransaction?
|
|
|
|
|
Hi,
I have to transport a big database table and can't read it at once with "select * from table" because the table is bigger than my system memory.
Is there a way to read the table step by step? I thought it was possible with ADO and his serverside cursors but I don't now how. I need an "universal" solution that works on SQL Server 2000/2005, MySQL and Oracle.
Regards,
hairy
|
|
|
|
|
Hello,
ROW_NUMBER(), PARTITION BY may be used for this...btw why you want multiple times I/O solution when you can do it in one...and why you are worrying about the system memory its what OS is there for...am still not convinced of your idea!
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Adeel is right - your ? is not well formed. Memory is the least of your problems. Disk IO, network latency and trafic volumes will play a larger role in your issue.
If you have a large table/database to "transport" I suggest you investigate other transport methods other than read it into a UI and write it into another database. One method which will probably be faster is to BCP the table in/out of the databases.
Each database type has different methods available, I suppose the UI path may be valid if you are looking for a DB agnostic solution. I suggest some furhter research, try asking for recommendations of moving table/s xxx big between db1, db2 and db3 you might get some enlightment.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
