CODEWITHSUNDEEP
sqlsql-serverinner-joindistinctwhere-clause
gadfly
gadfly

Reputation: 42

adding another where condition on inner join query

i got an issue with selecting data from below query based on mtb.mid . all i want is to select below query base on highest mtb.mid but i cant figure it out where do i need to put that cause on my where clause or other place ....

here is my fine and working query that give me result that i need to add a filter that i talked above .

SELECT DISTINCT TOP (100) PERCENT wTB.Week, wTB.Description, wTB.wid, mTB.mid
FROM           yTB INNER JOIN
                         mTB ON yTB.yid = mTB.yid INNER JOIN
                         wTB ON mTB.mid = wTB.mid INNER JOIN
                         dTB ON wTB.wid = dTB.wid
WHERE        (dTB.dateEn <= CAST(GETDATE() AS DATE))
ORDER BY wTB.Week

Thanks in advance.

Upvotes: 0

Views: 471

Answers (1)

MUHAMMAD TASADDUQ ALI
MUHAMMAD TASADDUQ ALI

Reputation: 387

This will work.

SELECT DISTINCT TOP (100) PERCENT wTB.Week, wTB.Description, wTB.wid, mTB.mid
FROM           yTB INNER JOIN
                         mTB ON yTB.yid = mTB.yid INNER JOIN
                         wTB ON mTB.mid = wTB.mid INNER JOIN
                         dTB ON wTB.wid = dTB.wid
WHERE        (dTB.dateEn <= CAST(GETDATE() AS DATE)) AND 
             mTB.mid = (SELECT MAX(mid)
                        FROM mTB)
ORDER BY wTB.Week

just put that condition in WHERE clause and use Subquery to get highest mTB.mid value from mTB table.

Upvotes: 1

Related Questions