CODEWITHSUNDEEP
sqlsql-servert-sql
Chicharito
Chicharito

Reputation: 1440

How to optimize this sql query and shorten?

I have two tables and whant to optmize the query below. I want to same line small photo and big photo columns.

    Table Projects

    ProjectID | ProjectName | ProjectContent
    ----------------------------------------
       2      | Test        | Test

    Table Media



    RID | MediaTypeID | FilePathAndName  | ProjectID
    ------------------------------------------------
    1   |      1      | big_photo.jpeg   |    2
    2   |      2      | small_photo.jpeg |    2



    SELECT
        T1.ProjectID,
        T1.ProjectName,
        T1.ProjectContent,
        T1.FilePathAndName AS BigPhoto,
        T2.FilePathAndName AS SmallPhoto
    FROM
    (
        SELECT 
            P.ProjectID,
            P.ProjectName,
            P.ProjectContent,
            M.FilePathAndName,
            ROW_NUMBER() over(ORDER BY M.RID) AS RN             
        FROM 
            Projects (NOLOCK) AS P
            INNER JOIN Media (NOLOCK) AS M ON M.RID = P.ProjectID AND M.MediaType = 1       
        WHERE
            P.ProjectID = 2
    ) AS T1,
    (       
        SELECT 
            P.ProjectID,
            P.ProjectName,
            P.ProjectContent,
            M.FilePathAndName,
            ROW_NUMBER() over(ORDER BY M.RID) AS RN             
        FROM 
            Projects (NOLOCK) AS P
            INNER JOIN Media (NOLOCK) AS M ON M.RID = P.ProjectID AND M.MediaType = 2       
        WHERE
            P.ProjectID = 2
    ) AS T2
    WHERE 
        T1.RN = T2.RN

Result Result:

Upvotes: 1

Views: 47

Answers (1)

Lamak
Lamak

Reputation: 70638

If I understand your code correctly, I think that you don't need the join nor the ROW_NUMBER:

SELECT 
    P.ProjectID,
    P.ProjectName,
    P.ProjectContent,
    MIN(CASE WHEN  M.MediaType = 1 THEN M.FilePathAndName END) AS BigPhoto,
    MIN(CASE WHEN  M.MediaType = 2 THEN M.FilePathAndName END) AS SmallPhoto
FROM 
    Projects (NOLOCK) AS P
    INNER JOIN Media (NOLOCK) AS M ON M.RID = P.ProjectID       
WHERE
     M.MediaType IN (1,2) AND P.ProjectID = 2
GROUP BY 
    P.ProjectID,
    P.ProjectName,
    P.ProjectContent;

Upvotes: 1

Related Questions