Jmorte13 Jmorte13 - 6 months ago 22
SQL Question

SQL ACCESS Subquery with extra AND condition

I have this SQL query for Acccess 2013 but I would like to have an extra condition saying: WHERE CLASS = 'ECN'.

I can't find a way to integrate it into my existing query - hopefully someone can give me a hint.

SELECT m.ID, m.PN, m.CLASS, m.ZAHL, (
SELECT Count(*)
FROM Table1 AS m2
WHERE m2.PN = m.PN AND m2.ID <= m.ID
) AS IncByPN
FROM Table1 AS m
ORDER BY m.ID;


So I want the result table to only show rows where CLASS = 'ECN'

Answer

Try something like this:

SELECT m.ID,
    m.PN,
    m.CLASS,
    m.ZAHL,
    Count(M2.id) AS IncByPN
FROM Table1 AS m
    INNER JOIN Table1 AS m2 ON m2.PN = m.PN AND m2.ID <= m.ID
WHERE m.CLASS='ECN'
GROUP BY m.ID, m.PN, m.CLASS, m.ZAHL
ORDER BY m.ID
Comments