Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_MissingBooks`(p_UsrID BIGINT ,
      p_Product VARCHAR(20))
BEGIN


        DECLARE p_LinkedBoard INT;
        DECLARE p_LinkedSubjects INT;
        DECLARE p_LinkedClasses INT;
        DECLARE p_LinkedBoards INT;
        DECLARE p_SchoolBooks INT;

        SELECT COUNT(*) INTO p_LinkedBoard
        FROM    userboards
        WHERE   userboards.usrid = p_UsrID;
        SELECT COUNT(*) INTO p_LinkedClasses
        FROM    userclasses
        WHERE   userclasses.UsrID = p_UsrID;
        SELECT COUNT(*) INTO p_LinkedSubjects
        FROM    usersubjects
        WHERE   usersubjects.usrid = p_UsrID;
        SELECT COUNT(*) INTO p_LinkedBoards
        FROM    schoolboards;
        SELECT COUNT(*) INTO p_SchoolBooks
        FROM    SchoolBooks;
        Select * From temp_MatchList;

        CREATE TEMPORARY TABLE temp_MatchList(ID BIGINT ,
            Year INT ,
            Name NVARCHAR(100) ,
            SyllabusDisplayName NVARCHAR(100) ,
            BookType NVARCHAR(20) ,
            ClassName NVARCHAR(20) ,
            ClassID INT ,
            ClassNumber INT ,
            BookName NVARCHAR(100) ,
            Board NVARCHAR(100) ,
            Publisher NVARCHAR(100) ,
            SubjectName NVARCHAR(100) ,
            SubjectID INT ,
            BoardID INT ,
            STATUS CHAR(1) ,
            BoardStatus CHAR(1)
        );


    INSERT INTO temp_MatchList 
     SELECT * FROM 
     (
        SELECT vBooksDetails.* 
        FROM
        (
           SELECT DISTINCT * FROM 
           (
              SELECT 
                classmaster.ClassID AS ClassID,
                CDisplayName,
                subjectmaster.id ,
                SubjectID, 
                subjectmaster.DispName ,
                BookName 
              FROM classmaster 
              JOIN subjectclasslinkage ON 
                 classmaster.ClassID = subjectclasslinkage.ClassNumber
             JOIN subjectmaster ON 
                 subjectclasslinkage.SubjectId = subjectmaster.id 
             LEFT JOIN 
              (
                 SELECT * FROM vBooksDetails 
                 WHERE 
                 (
                   (
                     (p_LinkedBoard = 0 ) OR (BoardID IN (SELECT boardname 
    FROM userboards WHERE userboards.usrid = p_UsrID ))) 
    AND ((p_LinkedBoards = 0 ) OR (BoardID IN (SELECT BoardID FROM schoolboards ))) 
    AND EXISTS (SELECT * FROM vBoardInfo WHERE ID = BoardID )AND vBooksDetails.Status = 'A'))
    lessonsyllabus ON subjectmaster.id = lessonsyllabus.SubjectID AND 
    lessonsyllabus.ClassID = classmaster.ClassID ) TAB WHERE TAB.BookName IS NULL ) TAB_MISSING 
    JOIN vBooksDetails ON TAB_MISSING.ClassID = vBooksDetails.ClassID 
    AND TAB_MISSING.SubjectID = vBooksDetails.SubjectID AND vBooksDetails.BoardID = 3 
    WHERE   vBooksDetails.Status = 'A' AND ((p_LinkedClasses = 0 ) 
    OR ( vBooksDetails.ClassID IN(SELECT classid FROM userclasses WHERE UsrID = p_UsrID ))) 
    AND (( p_LinkedSubjects = 0 ) OR (vBooksDetails.SubjectID IN (SELECT subjectid FROM usersubjects
    WHERE usrid = p_UsrID ))) 
        -- AND ((@LinkedBoard = 0) OR (BoardID IN (SELECT boardname FROM userboards WHERE usrid=@UsrID)) )
    AND ((p_SchoolBooks = 0 ) OR ( vBooksDetails.Id NOT IN ( SELECT BookID FROM SchoolBooks))));

END
share|improve this question

1 Answer 1

As far as I know the part:

{...}
SELECT * FROM (SELECT vBooksDetails.* FROM
{...}
);

could have caused the error - the topmost select wasn't aliased.

Please try this version of your last big insert:

INSERT INTO 
    temp_MatchList
SELECT 
    vBooksDetails.* 
FROM
(
    SELECT DISTINCT * 
    FROM 
    (
        SELECT 
            classmaster.ClassID AS ClassID,
            CDisplayName,
            subjectmaster.id,
            SubjectID, 
            subjectmaster.DispName,
            BookName 
        FROM 
            classmaster 
            JOIN subjectclasslinkage 
                ON classmaster.ClassID = subjectclasslinkage.ClassNumber
            JOIN subjectmaster 
                ON subjectclasslinkage.SubjectId = subjectmaster.id 
            LEFT JOIN 
            (
                SELECT * 
                FROM vBooksDetails 
                WHERE 
                (
                    ((p_LinkedBoard = 0 ) 
                      OR (BoardID IN (SELECT boardname FROM userboards WHERE userboards.usrid = p_UsrID ))) 
                    AND ((p_LinkedBoards = 0 ) OR (BoardID IN (SELECT BoardID FROM schoolboards ))) 
                    AND EXISTS (SELECT * FROM vBoardInfo WHERE ID = BoardID )
                    AND vBooksDetails.Status = 'A')
            ) lessonsyllabus 
            ON subjectmaster.id = lessonsyllabus.SubjectID 
               AND lessonsyllabus.ClassID = classmaster.ClassID 
    ) TAB 
    WHERE 
        TAB.BookName IS NULL 
) TAB_MISSING 
    JOIN vBooksDetails 
        ON TAB_MISSING.ClassID = vBooksDetails.ClassID 
           AND TAB_MISSING.SubjectID = vBooksDetails.SubjectID 
           AND vBooksDetails.BoardID = 3 
WHERE   
    vBooksDetails.Status = 'A' 
    AND ((p_LinkedClasses = 0 ) 
         OR ( vBooksDetails.ClassID IN (SELECT classid FROM userclasses WHERE UsrID = p_UsrID ))) 
    AND (( p_LinkedSubjects = 0 ) 
         OR (vBooksDetails.SubjectID IN (SELECT subjectid FROM usersubjects WHERE usrid = p_UsrID ))) 
-- AND ((@LinkedBoard = 0) OR (BoardID IN (SELECT boardname FROM userboards WHERE usrid=@UsrID)) )
    AND ((p_SchoolBooks = 0 ) 
         OR ( vBooksDetails.Id NOT IN ( SELECT BookID FROM SchoolBooks)));
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.