|
Hi to all
Could anyone please help me with a sql query.
I am using MS Access database with Vb.net2005
I have 3 table student,class_held,attendance with the following fields
1. student table (contains the details of students)
id (primary key)
fname
lname
batch_id
.
.
2.class_held (contains the details of each class held)
class_id (primary key)
date
batch_id
subject
3.attendance (contains the details of only absentees student) [there is no primary key I have given, class_id can be same for n number of students who are absent for a particular class ].[absentees are nothing but the student_id in the student table]
class_id
absentees
Now I want the list of students who are present for a particular class.
Thanks in advance
Regards,
HImangshuS
I am a learner Always
|
|
|
|
|
Sounds like homework...
However, is the idea that every student should be on every class??? If that's correct you could use cartesian product to generate all combinations and then use NOT IN clause to check that the user wasn't marked as absent.
BTW, posting the query you have already tried (you have tried, haven't you) would help a lot.
|
|
|
|
|
Thanks for your reply Mika,
This is not a homework but for my college project.
Anyway,I have already done the Cartesian product and got all the combinations. But, I don't know how to use NOT IN clause with 2 condition (because I need to check for the student Id as well as class_id).
Regards,
Himangshu S
I am a learner Always
|
|
|
|
|
Perhaps it would be easiest to use something like:
...
WHERE NOT EXISTS( SELECT 1
FROM attendance
WHERE attendance.class_id = class_held.class_id
AND attendance.absentees = student.id)
|
|
|
|
|
Hello Mika,
Thanks a lot...I got that correct..
Regards,
HimangshuS
I am a learner Always
|
|
|
|
|
Hi,
I am having database files in .dbf format.I am new to Foxpro database.Now i want to convert those foxpro database files to Sql server. Can you guide me is there any other tools to convert .Dbf files to Sql server(.mdf,.ldf).Its very urgent to me if you know please suggest me.
Thanks in Advance
Pavani
|
|
|
|
|
|
hi all,
i want to create a trigger on table through a stored procedure but i got error in excuting the script
it say syntax problem near word TRIGGER
CREATE PROCEDURE CreateMyTrigger
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TRIGGER [dbo].[InsertQuestionTrigger]
ON [dbo].[Question]
FOR INSERT AS
UPDATE child
-- set the depth of this "child" to be the
-- depth of the parent, plus one.
SET depth = ISNULL(parent.depth + 1,0),
-- the lineage is simply the lineage of the parent,
-- plus the child's ID (and appropriate '/' characters
lineage = ISNULL(parent.lineage,'/') + LTrim(Str(child.QuestionId)) + '/'
-- we can't update the "inserted" table directly,
-- so we find the corresponding child in the
-- "real" table
FROM Question child INNER JOIN inserted i ON i.QuestionId=child.QuestionId
-- now, we attempt to find the parent of this
-- "child" - but it might not exist, so these
-- values may well be NULL
LEFT OUTER JOIN Question parent ON child.IdParent=parent.QuestionId
END
GO
Any Idea and how can create trigger using function or stored procedure.
Thank you in advance!
|
|
|
|
|
Instead of writing the trigger creation directly to the stored procedure, put it in a varchar variable. Then you can use EXECUTE[^] to execute the string.
|
|
|
|
|
for example;
declare @query varchar(500)
set @query = "your trigger create here, I would suggest using not exists clause to prevent you from trying to create it when it is already created  "
EXECUTE @query
Good luck
|
|
|
|
|
Hi All,
I know "TOP" Function can select the TOP n records. How can I select records from for example 20 to 50? I know SQL server doesn't have "LIMIT" Function....
Thanks
Tin
|
|
|
|
|
|
Thanks for your reply, I able to create the row number column but when I tried to select a range of row number. It appeared this error message "
Conversion failed when converting the varchar value 'Row_Number' to data type int"
I have used cast and convert already.
Any idea?
Tin
|
|
|
|
|
Could you post the query so it would be easier to see the problem
|
|
|
|
|
Hi,
what you oculd also try is something like this...
SELECT top(20)* FROM _yourtable
WHERE _tableID not in (select top(20) tableID from _yourtable)
|
|
|
|
|
Hi friends,
Is any tutorial or useful link for stored procedure optimization with example.
I searched in google.But it is not enough. Please if you know send me,
Hope your's reply.
Thanks & Regards
Every Successful Person Have A Painful Story
|
|
|
|
|
|
Hi mik
Thanks for yours reply.
Every Successful Person Have A Painful Story
|
|
|
|
|
I have been listening so many different answer to question of favorite database. I would like to know what is favorite database of SQLAuthority.com readers. I encourage all of you to participate in this poll. It is very quick poll and takes a second.
A gift of USB drive will be provided to user who will also add comment discussing this subject matter. I encourage to send emails to your friends to participate in poll. I am hopping this poll becomes landmark poll for people who are finding answer to favorite database.
http://blog.sqlauthority.com/2009/02/25/the-poll-what-is-your-favorite-database/[^]
Kind Regards,
Pinal Dave
|
|
|
|
|
pinaldave wrote: I would like to know what is favorite database of SQLAuthority.com readers. I encourage all of you to participate in this poll.
Since I don't read SQLAuthotity.com, I only heard of it just a moment ago, if I participated then it would skew your results. It would no longer be a poll of favourite databases of SQLAuthority.com readers.
|
|
|
|
|
Hi,
It is not for my readers only.I would like to collect data from every developer in world.
Please participate in poll.
Kind Regards,
Pinal
|
|
|
|
|
pinaldave wrote: I would like to collect data from every developer in world
Good luck with that. Most marketing companies have the view that if they get a response from 1% of the audience they are doing exceptionally well.
Heck! Many elections can struggle to get 50% turn out.
|
|
|
|
|
Hi friend,
I have a doubt. Could you clear my doubt?
Actually what is raising events available in stored procedure?
If it is yes, how can we implement and test?
Is any useful link or tutorial available for raising events in sp?
Hope yours reply.
Thanks & Regards
Every Successful Person Have A Painful Story
|
|
|
|
|
Programming in T-SQL isn't event based so you cannot use events like you would in Visual Studio. However, yuo can use for example Queues[^].
|
|
|
|
|
In stored procedure we use sp_sendmail and send sms, other notification are all events?What about that.
Every Successful Person Have A Painful Story
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
