Categories

See More
Popular Forum

MBA (4887) B.Tech (1769) Engineering (1486) Class 12 (1030) Study Abroad (1004) Computer Science and Engineering (988) Business Management Studies (865) BBA (846) Diploma (746) CAT (651) B.Com (648) B.Sc (643) JEE Mains (618) Mechanical Engineering (574) Exam (525) India (462) Career (452) All Time Q&A (439) Mass Communication (427) BCA (417) Science (384) Computers & IT (Non-Engg) (383) Medicine & Health Sciences (381) Hotel Management (373) Civil Engineering (353) MCA (349) Tuteehub Top Questions (348) Distance (340) Colleges in India (334)
See More

Should i use LEFT JOIN OR INNER JOIN or RIGHT JOIN for joining 3 different query into a single query

Course Queries Syllabus Queries

Max. 2000 characters
Replies

usr_profile.png

User

( 4 months ago )


FIRST QUERY

$sql="SELECT distinct(syllabus.syllabusName) as subjectName,$cid as courseId, stud.batchId as batchId,course.courseId,course.syllabusId AS subjectID,course.unitId,stud.studentID as studentID  ,concat(stud.studentFirstName,' ',stud.studentLastName,' ',stud.studentRegistrationNumber) AS studentName
            FROM studentdetails  stud           
            INNER JOIN  `coursemapping` course 
            ON  stud.`courseId`=course.courseId
            INNER JOIN syllabus syllabus
            ON  course.syllabusId=syllabus.syllabusId 
            WHERE course.courseId ='$cid' AND course.syllabusId='$sid' AND stud.`batchId`='$bid'";  

SECOND QUERY

     $sql=" SELECT A.assignmentId, A.unitId, B.assignmentName, C.assignmentsBatchId AS batchId, C.courseId AS courseId,C.assignmentMappingId, D.studentId, concat( E.studentFirstName, ' ', E.studentLastName, ' ', E.studentRegistrationNumber ) AS studentName
            FROM studentdetails E
            INNER JOIN assignmentscompleted D ON E.studentId = D.studentId
            INNER JOIN assignmentsbatch C ON D.assignmentId = C.assignmentMappingId
            INNER JOIN assignments B ON D.assignmentId = B.assignmentId
            INNER JOIN assignmentsMapping A ON B.assignmentId = A.assignmentId
            WHERE A.assignmentId = '$sid'
            AND C.assignmentsBatchId = '$bid'
            AND C.courseId ='$cid'";

THIRD QUERY

    $sql    =   "SELECT distinct(C.syllabusName) as subjectName,B.maxAccadamicMarks as marksScored,B.academicMarks as maxMarks,B.marksPercentage as percentage  
                    FROM studentdetails A INNER JOIN marks B 
                    ON A.studentId=B.studentId AND A.courseId=B.courseId AND A.batchId=B.batchId 
                    INNER JOIN syllabus C 
                    ON  B.syllabusId=C.syllabusId WHERE A.studentUserName='$studentUserName'";

Here i need to join all the three query in order to fetch the subject,student,accmarks,marks,percentage? how to join all this three query to single query

usr_profile.png

User

( 4 months ago )


It looks like you would likely be able to merge the queries together quite easily as many are using common tables, but without a table structure I am just writing a quick and dirty mashup or the three queries you have provided. I am assuming that each of these queries retuns exactly what you need and nothing else.

I have altered the third query just so slightly to add in the studentID so that it can be properly joined with the other queries and also commented out redundant information in the second query.

select
    a.studentID as studentID,
    a.studentName as studentName,
    $cid as courseID,
    a.batchID as batchID,
    a.subjectID as subjectID,
    c.subjectName as subjectName,
    a.unitID as unitID,
    b.assignmentID as assignmentID,
    b.assignmentName as assignmentName,
    b.assignmentMappingID as assignmentMappingID,
    c.marksScored as marksScored,
    c.maxMarks as maxMarks,
    c.percentage as percentageMarks
from
(
    select 
        distinct(syllabus.syllabusName) as subjectName,
        $cid as courseID, 
        stud.batchId as batchID,
        course.courseID,
        course.syllabusId as subjectID,
        course.unitID,
        stud.studentID as studentID  ,
        concat(stud.studentFirstName,' ',stud.studentLastName,' ',stud.studentRegistrationNumber) AS studentName
    FROM 
        studentdetails  stud           
            INNER JOIN  `coursemapping` course 
                ON  stud.`courseId`=course.courseId
            INNER JOIN syllabus syllabus
                ON  course.syllabusId=syllabus.syllabusId 
    WHERE 
        course.courseId ='$cid' 
        AND course.syllabusId='$sid' 
        AND stud.`batchId`='$bid'
) a,
(
    SELECT 
        A.assignmentID, 
        A.unitID, 
        B.assignmentName, 
        C.assignmentsBatchId AS batchID, 
        C.courseId AS courseID,
        C.assignmentMappingID, 
        D.studentID, 
        --concat( E.studentFirstName, ' ', E.studentLastName, ' ', E.studentRegistrationNumber ) AS studentName
    FROM 
        studentdetails E
            INNER JOIN assignmentscompleted D 
                ON E.studentId = D.studentId
            INNER JOIN assignmentsbatch C 
                ON D.assignmentId = C.

what's your interest