|
|
Mika Wendelius wrote: If you don't need a "byrocratic" installation, SQLite is most propably a good choice
Nuh, I prefer "graphitic" installs, they're much easier to rub out.
Thanks for doing my research, I put myself on the ximian mailing lists earlier this evening.
rgds PhilD
|
|
|
|
|
Hello everybody,
i have 3 tables Student, Instructor, and Allocation. I have 5 students and 2 instructors how can I allocate 2 instructors to 5 students for project supervision.
StudentId StudentName
1 Learner
2 Paul
3 Griffin
4 Inese
5 Kelly
InstructorId InstructorName
1 Sharma
2 Bill
this is out put table
AllocationId InstructorName StudentName
001 Sharma Learner
002 Sharma Paul
003 Bill Griffin
004 Bill Inese
005
where the Kelly will go?
please any body could give me a clue how should I do it either with queries or mathematical calculations.
let suppose if I write a stored procedure 'Allocation' which take two inputs students and instructors like Allocation 5,2 how it will work.
thnx in adv for any kind help.
regards
learner
|
|
|
|
|
usually, when creating this sort of association you use the ID fields from Student and Instructor table.
But you've answered your own question - If Kelly's instructor is Bill then:
001 Sharma Learner
002 Sharma Paul
003 Bill Griffin
004 Bill Inese
005 Bill Kelly
|
|
|
|
|
thnx for your response
actually it is my fault that i didn't explain properly. Allocation table is only the format that how i need to display it but I'm not sure how can I get this format as (Allocation Table)
cos I got double minded that I should use joins or should i do mathematical calculation first like this
create proc AllocatIns
as
Declare @NumOfRec int,@NumOfRe int, @Result float
select @NumOfRec= count(*) from Student
select @NumOfRe= count(*) from Instructor
Print @NumOfRec
Print @NumOfRe
select @Result =@NumOfRec % @NumOfRe
Print @Result
if you give me the idea how to allocate instructor to students that would be helpful. cos it is all automated process no need for operator to allocate one by one. as we do in GUI Number of students should be assigned from dropdownlist. may be I m not thinking in right direction.
thanx in adv.
regards
learner
|
|
|
|
|
You've still provided a shockingly bad description of your problem and your naming of variables does not help! I think you want to allocate any given number of students to an instructor.
If that is right the process is easy. Insert x number of students who have not been allocated an instructor. I have assumed you hgave changed the Allocation table to hold the StudentId and InstructorId as already suggested. I have also assumed that AllocationId is an identity field.
CREATE PROC AllocateInstructor
@numStudents INT,
@InstructorId INT
AS
INSERT INTO Allocation (InstructorId, StudentId)
SELECT TOP @numStudents
@instructorId,
studentId
FROM Students
WHERE studentId NOT IN(SELECT studentId FROM Allocation)
|
|
|
|
|
your assumption is absolutely fine.
let me try to explain this time may be better this time
we have two table student and instructor
number of records can be change of both student and Instructor tables
lets suppose
Number of students Number of Instructor
30 11
42 9
21 13
16 5
1-what we need to do we have to allocate 11 instructor to 30 students.
2- 9 instructor to 42 students.
3- 13 instructor to 21 students.
4- 5 instructor to 16 students.
let suppose 1,2,3,and 4 represents 2001,2002,2003,2004.
every year number of students and instructor changes and we have to allocate instructor to new students.
any way I really appreciate your patience and time you gave to understand my requirements.
regards
learner.
|
|
|
|
|
How to start a stored procedure, type code and End it? And how to execute the procedure in SQL? Can anyone explain it?
|
|
|
|
|
whether you try to search on google ? if no, then click Google[^]
|
|
|
|
|
|
I think you really need to buy a book, or at least google for some examples as this is one of the most basic parts of database programming. If you don't know this, I suspect you will not know what T-SQL to use in it either.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi Team,
We need a SSIS functional to move file from source (Local path) to FTP location. (Destination )
The task have been done successfully and move file via objects FTP TASK and ForEach Control provided by SSIS.
Now the given functional is quiet changes, that is we need to create folder date wise dynamically on FTP and move the files.
So could it be feasible to achieve the target and help me out from this scenario.
Thanks and Regards
Tarun Singh
tksingh@zenta.com
|
|
|
|
|
Hi friends
I am getting an error (unknown 0x80005000)like this while installing SQL server 2008 in my system .can u give me some solutions.
regards
B.anand
|
|
|
|
|
Check in your Windows services that Windows Installer Service is running. Also remember that you have to install Windows Installer 4.5[^].
|
|
|
|
|
SELECT DISTINCT Firms.dbo.FirmNames.id, Firms.dbo.FirmNames.FirmName
FROM FirmsInFamily INNER JOIN
Families ON FirmsInFamily.FamilyID = Families.FamilyID INNER JOIN
Firms.dbo.FirmNames ON FirmsInFamily.FirmID <> Firms.dbo.FirmNames.id
WHERE (Families.FamilyID = @FamilyID)
I guess this would work if FirmsInFamily.FirmID only had one entry, but there is a possibility of there being multiple records for this field so how can I remove the records that don't belong because they are located in the FirmsInFamilyTable from the Firms.dbo.FirmNames.
I'm lookin for something that is extremely quick as this search is going to be done quite a bit.
I tried doing a derived table and then deleting, but I guess you can't do that.
Thanks in advance.
Franklin Smith
|
|
|
|
|
You could have a look, what kind of execution plan comes out of something like this. Also check if this gives the desired result (I understood that the original query didn't):
SELECT Firms.dbo.FirmNames.id, Firms.dbo.FirmNames.FirmName
FROM Firms.dbo.FirmNames
WHERE Firms.dbo.FirmNames.id NOT IN
(SELECT FirmsInFamily.FirmID
FROM FirmsInFamily
INNER JOIN Families
ON FirmsInFamily.FamilyID = Families.FamilyID
WHERE Families.FamilyID = @FamilyID)
|
|
|
|
|
Thanks worked like a charm.
SQL sometimes reacts so different from the way I would expect.
I really need to take an indepth class on it.
|
|
|
|
|
No problem 
Yeah, SQL is a funny language. It takeas about 5 hours to learn it and about 50 years to master it, so still learning...
Sunset Towers wrote: I really need to take an indepth class on it.
Don't know if these would be useful, but I kinda like Joe Celko's books. They contain good tips and tricks if you're already familiar with the basics. Have a look at for example these: http://www.amazon.com/s/ref=nb_ss_gw?url=search-alias%3Daps&field-keywords=joe+celko[^]
|
|
|
|
|
Hello everybody,
please could anyone help me to find out answer the following scenario.
in abc college there are 30 students and 11 instructors. How I can allocate 11 instructors to 30 students automatically for their project supervision. I understand it is a mathematics question. if I get the mathematical formula I'll be able to implement this in SQL. thanks in adv for any kind help.
regard
learner
|
|
|
|
|
Hey guys, this is probably a stupid syntax error but i can seem to see what I'm doing wrong 
USE master
GO
-- Drop the database if it already exists
IF EXISTS
(
SELECT name
FROM sys.databases
WHERE name = N'RoomsForRentDB'
)
DROP DATABASE RoomsForRentDB
GO
CREATE DATABASE RoomsForRentDB
GO
USE RoomsForRentDB
GO
CREATE TABLE [User]
(
UserID BIGINT IDENTITY(0, 1) NOT NULL,
EmailAddress VARCHAR(1000) NOT NULL,
[Password] VARCHAR(1000) NOT NULL,
CONSTRAINT PK_UserID PRIMARY KEY(UserID)
)
GO
CREATE TABLE House
(
HouseID BIGINT IDENTITY(0, 1) NOT NULL,
UserID BIGINT REFERENCES [User].UserID NOT NULL,
CONSTRAINT PK_HouseID_UserID PRIMARY KEY(HouseID, UserID)
)
GO
Error
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK__House__UserID__014935CB' references invalid table 'User.UserID'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
I'm using SQL 2008 if that makes a difference
Thanks
Harvey Saayman - South Africa
Software Developer
.Net, C#, SQL
you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer)
1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111
|
|
|
|
|
Instead of:
UserID BIGINT REFERENCES [User].UserID NOT NULL,
you should have:
UserID BIGINT REFERENCES [User] (UserID) NOT NULL,
Harvey Saayman wrote: CONSTRAINT PK_HouseID_UserID PRIMARY KEY(HouseID, UserID)
Also, do you really want to include UserId to primary key of House? I think there's no need since HouseId is already IDENTITY and cannot have duplicate values.
Mika
|
|
|
|
|
Mika Wendelius wrote: REFERENCES [User] (UserID) NOT NULL,
They changed SQL's syntax in 2008!? 
Mika Wendelius wrote: Also, do you really want to include UserId to primary key of House?
There's no specific reason, I'm just used to doing it like that.
Thanks for the help Mika, I'll only be able to test it when i get home tonight, I'll let you know how it goes
Harvey Saayman - South Africa
Software Developer
.Net, C#, SQL
you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer)
1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111
|
|
|
|
|
Harvey Saayman wrote: They changed SQL's syntax in
Don't know if the syntax you used worked in previous versions. The one I used is actually ANSI syntax so SQL specific syntaxes me be dropped.
Harvey Saayman wrote: There's no specific reason, I'm just used to doing it like that.
If you may, I'd suggest that you would use only the identity column as primary key. This will ease yuor dml operations as well as queries (you don't have to use two fields in statements and the primary key index will be more efficient.
Also if you use foreign keys to refer to House, you would only have to use HouseID. This would be benefitial to all foreign keys and especially CASCADE operations.
Harvey Saayman wrote: I'll let you know how it goes
That would be nice.
Harvey Saayman wrote: Thanks for the help Mika
No problem
|
|
|
|
|
I'm a moron...
I just checked a create database script i wrote about a year ago(I didn't have that or my SQL books with me at home when i wrote the OP) and your right, its supposed to be REFERENCES House(HouseID) NOT NULL
Of all the things I've lost I miss my mind the most...
Harvey Saayman - South Africa
Software Developer
.Net, C#, SQL
you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer)
1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111
|
|
|
|
|
Don't worry. It happens to everyone enery now and then
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
