February 17, 2012 at 8:30 am
here iam having 3table which has inner joins to each others
TABLE1
QuestionPaperId (PK) QuestionPaper Duration NOOfQuestions MarksPerQuestion MinimumMarks
1 ccna 20 2 5 5
2 net 30 5 10 20
TABLE2
QuestionId (PK) QuestionS QuestionPaperId (FK)
1 something ? 1
2 something1 ? 1
3 thing1 ? 2
4 thing2 ? 2
5 thing3 ? 2
6 thing4 ? 2
7 thing5 ? 2
TABLE3
ScheduledExamsId (PK) DateOfExam QuestionPaperId (FK)
1 2012-02-17 1
2 2012-02-18 2
(I NEED TO WIRTE STORE PROCEDURE FOR THIS( TABLE3 )ONLY IF I VIEW THE TABLE 3
IT SHOULD DISPLAY OUT PUT AS I MENTIONED AFTER THIS TABLE)
THE OUTPUT SHOULD BE LIKE THIS
ScheduledExamsId DateOfExam QuestionPaper MinimumMarks ( maximummark )
1 2012-02-17 ccna 5 10
2 012-02-18 net 20 50
I TRIED SAMPLE STOREPROCEDURE
DECLARE @prod INTEGER
SELECT @prod = MarksPerQuestion* NumberOfQuestions FROM dbo.tblQuestionPaper
select
m.ScheduledExamsId,
m.DateOfExam,
l.QuestionPaper,
@prod as'maximummark' ,
l.MinimumMarks
from
tbl TABLE3 m
inner join
tblTABLE1 l
on
l.QuestionPaperID = m.QuestionPaperId
BUT THIS OUT PUT IS WRONG
ScheduledExamsId DateOfExam QuestionPaperName MinimumMarks (maximummark)
1 2012-02-17 ccna 5 25
2 2012-02-17 net 5 25
ANOTHER WAY ALSO I TRIED
declare @QuestionPaperId int
set @QuestionPaperId=0
DECLARE @prod3 INTEGER
set @prod3=@QuestionPaperId+1
select COUNT(QuestionPaperId) from TABLE2 where QuestionPaperId=@prod3
SO PLEASE ANY SOLVE MY PROBLEM AND GIVE THE CORRECT VIEW STORE PROCEDURE....
February 17, 2012 at 8:37 am
You have tried to write stored proc? - Good!
Could you please try to add tables DDL's with sample data insert script to help your helpers to help you 🙂
Link at the bottom of my signature will lead you to this forum "etiquette" suggestions.
February 17, 2012 at 8:50 am
Hello and welcome to SSC!
First, when you ask a question you get more responses when you provide us with DDL and sample data. This time, I've done it for you: -
CREATE TABLE TABLE1 (QuestionPaperId INT IDENTITY PRIMARY KEY, QuestionPaper VARCHAR(4), Duration INT,
NOOfQuestions INT, MarksPerQuestion INT, MinimumMarks INT)
INSERT INTO TABLE1
SELECT QuestionPaper, Duration, NOOfQuestions, MarksPerQuestion, MinimumMarks
FROM (VALUES('ccna',20,2,5,5),('net',30,5,10,20)) a(QuestionPaper, Duration, NOOfQuestions, MarksPerQuestion, MinimumMarks)
CREATE TABLE TABLE2 (QuestionId INT IDENTITY PRIMARY KEY, QuestionS VARCHAR(12), QuestionPaperId INT)
INSERT INTO TABLE2
SELECT QuestionS, QuestionPaperId
FROM (VALUES('something ?',1),('something1 ?',1),('thing1 ?',2),('thing2 ?',2),('thing3 ?',2),
('thing4 ?',2),('thing5 ?',2)) a(QuestionS, QuestionPaperId)
CREATE TABLE TABLE3 (ScheduledExamsId INT IDENTITY PRIMARY KEY, DateOfExam DATE, QuestionPaperId INT)
INSERT INTO TABLE3
SELECT DateOfExam, QuestionPaperId
FROM (VALUES('2012-02-17',1),('2012-02-18',2))a(DateOfExam, QuestionPaperId)
Now, using that sample data how do we get your result? Easy. . . some INNER JOINs and a SUM.
SELECT a.ScheduledExamsId, a.DateOfExam, c.QuestionPaper, c.MinimumMarks, SUM(c.MarksPerQuestion) AS maximummark
FROM TABLE3 a
INNER JOIN TABLE2 b ON a.QuestionPaperId = b.QuestionPaperId
INNER JOIN TABLE1 c ON b.QuestionPaperId = c.QuestionPaperId
GROUP BY a.ScheduledExamsId, a.DateOfExam, c.QuestionPaper, c.MinimumMarks
Which returns:
ScheduledExamsId DateOfExam QuestionPaper MinimumMarks maximummark
---------------- ---------- ------------- ------------ -----------
1 2012-02-17 ccna 5 10
2 2012-02-18 net 20 50
February 17, 2012 at 1:39 pm
sorry for your inconvenience and thanks a lot for your work
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply