Post reply

Exclude from table

  • September 15, 2004 at 3:29 am

    #59326

    I have the following view and table

    vwMatched

    userId

    jobId

    otherFields

    tblExclude

    userId

    jobId

    the exclude table is a lookup table of jobs that have been matched to a user that they are not interested in. How can I query my view to retrieve ALL userId and job combinations UNLESS that user and that job combination is in the exclude table?

    Cheers,

  • September 15, 2004 at 8:11 am

    #523171

    SELECT A.userId, B.jobId

    FROM (SELECT DISTINCT userId FROM vwMatched) A

    CROSS JOIN (SELECT DISTINCT jobId FROM vwMatched) B

    WHERE NOT EXISTS

    (SELECT *

    FROM tblExclude

    WHERE userId = A.userId

    AND jobId = A.jobId)

    --
    Adam Machanic
    whoisactive

  • September 15, 2004 at 10:11 am

    #523202

    OR

    SELECT A.*

    FROM vwMatched A left join tblExclude E on E.userId = A.userId

    AND E.jobId = A.jobId

    WHERE  E.userId  is Null


    * Noel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply