CODEWITHSUNDEEP
sqljoin
Cool
Cool

Reputation: 35

Inner query and joins

I have written a query to get a code from table A which is not in table B for same ID. I have hardcoded the ID in the query, I want to know how to get the desired output for all the IDs not for 539 alone. Below is the query

SELECT
    a.code
FROM
    A
WHERE
    a.code NOT IN(
        SELECT
            b.code
        FROM
            B b
        WHERE
           b.ID='539'
        AND b.status <> 'D')
AND 
     (a.id='539')
AND 
     (a.status <> 'D')

Upvotes: 3

Views: 82

Answers (2)

Matt Smucker
Matt Smucker

Reputation: 5234

Join the tables together with a LEFT join and then only select records that do not have a corresponding record in table B

SELECT a.code
FROM A
LEFT JOIN B
  ON a.ID = b.ID
  AND a.code = b.code
  AND b.status <> 'D'
WHERE a.status <> 'D'
AND b.code IS NULL

Upvotes: 2

Catfish
Catfish

Reputation: 19284

I think you should be able to just remove the id's that you're querying on and you should get everything.

SELECT
    a.code
FROM
    A
WHERE
    a.code NOT IN(
        SELECT
        b.code
        FROM
        B b
        AND b.status <> 'D')
and AND a.status <> 'D'

Upvotes: 0

Related Questions