SQLTeam.com | Weblogs | Forums

QUERY help


#1

Hello,

I have a basic sql statement as listed below:

SELECT Sales_Manager, Plan_ID
FROM SalesTBL

And here is my result set:
Sales_Manager Plan_ID
Martin Stoner 1A
Richard Mark 1A
Melissa Smile 1A
Jacky Fung 2B
Kirk Huge 1A

What I need to do is to return the dataset if Plan_ID listed as 1A only. Above record set has 2B so my return set would be nothing, it has to be all rows with 1A only.

How do I achieve that?

So, as long as the record sets has all rows as 1A then I will return something otherwise nothing, so it has to be all 1A.

Thanks all


#2
CREATE TABLE SalesTBL
    ([Sales_Manager] varchar(13), [Plan_ID] char(2))
;
    
INSERT INTO SalesTBL
    ([Sales_Manager], [Plan_ID])
VALUES
    ('Martin Stoner', '1A'),
    ('Richard Mark', '1A'),
    ('Melissa Smile', '1A'),
    ('Jacky Fung', '2B'),
    ('Kirk Huge', '1A')
;


SELECT
    *
FROM
    SalesTBL
WHERE
    NOT EXISTS(SELECT * FROM SalesTBL WHERE Plan_ID <> '1A')

Or:

SELECT
    *
FROM
    SalesTBL
WHERE
    '1A' = ALL (SELECT Plan_ID FROM SalesTBL)

#3

SELECT Sales_Manager, '1A' AS Plan_ID
FROM SalesTBL
WHERE NOT EXISTS(SELECT * FROM SalesTBL WHERE Plan_ID IS NULL OR Plan_ID <> '1A')