SQL Query to find Records companywise I want a query that will display rows of those COMPANIES who are giving theft cover and key loss Having status is yes
See the Sample Data below :

    addon       status  amt1    amt2    company
    theft cover yes      7       7      comp1
    key loss    yes      2       2      comp1
    theft cover no      NULL    NULL    comp2
    key loss    yes      2      33      comp2
    key loss    yes      1       1      comp3
    theft cover yes     12      22      comp3
    theft cover yes     11      22      comp4
    key loss    no      NULL    NULL    comp4
    theft cover yes     22      55      comp5
    key loss    yes     33      44      comp5


My query:

SELECT     addon, status, amt1, amt2, company
FROM         test
WHERE     (addon = 'theft cover' OR
                      addon = 'key loss') AND (status = 'yes')


Please help me. The above query is not working.
This query gives you company names:

SELECT company
FROM @Table
WHERE addon IN ('theft cover', 'key loss') AND status = 'yes'
GROUP BY company
HAVING COUNT(DISTINCT addon) = 2;



It gets all rows with requiered values (WHERE clause)
It GROUP BY company names in order to count how many of them are present
It only keeps companies with a COUNT of DISTINCT values of 2 (='theft cover'+'key loss')


Output:

company
comp1
comp3
comp5


You can then join it with you main table and get each associated rows:

SELECT t.addon, t.status, t.amt1, t.amt2, t.company
FROM @Table t
INNER JOIN (
    SELECT company
    FROM @Table
    WHERE addon IN ('theft cover', 'key loss') AND status = 'yes'
    GROUP BY company
    HAVING COUNT(distinct addon) = 2
) as c
    ON c.company = t.company
-- where t.compny = 'xxx'
-- where at.mt1 = 7 or t.amt2 = t.amt1
-- where ...
;


Output:

addon       status  amt1    amt2    company
theft cover yes     7       7       comp1   
key loss    yes     2       2       comp1   
key loss    yes     1       1       comp3   
theft cover yes     12      22      comp3   
theft cover yes     22      55      comp5   
key loss    yes     33      44      comp5   


Sample data:

Declare  @Table table([addon] varchar(20), [status] varchar(3), [amt1] varchar(4), [amt2] varchar(4), [company] varchar(5));

INSERT INTO @Table([addon], [status], [amt1], [amt2], [company])
VALUES
    ('theft cover', 'yes', '7', '7', 'comp1'),
    ('key loss', 'yes', '2', '2', 'comp1'),
    ('theft cover', 'no', NULL, NULL, 'comp2'),
    ('key loss', 'yes', '2', '33', 'comp2'),
    ('key loss', 'yes', '1', '1', 'comp3'),
    ('theft cover', 'yes', '12', '22', 'comp3'),
    ('theft cover', 'yes', '11', '22', 'comp4'),
    ('key loss', 'no', NULL, NULL, 'comp4'),
    ('theft cover', 'yes', '22', '55', 'comp5'),
    ('key loss', 'yes', '33', '44', 'comp5');