Join tables with some same values
If you want to see the records that have more than one value in Attribute_Value then you can't simply use filters on a WHERE because you want to validate rows in a group (a WHERE will filter by row, not by set).

Try using a GROUP BY with HAVING to check existance of multiple values for a particular set, then use this result to display the proper records.

SELECT
    D.Attribute_Name,
    D.ADAPTERID,
    D.Attribute_Value
FROM
    (
        SELECT 
            Attribute_Name,
            ADAPTERID 
        FROM 
            database1 
        WHERE
            Attribute_Value IN ('ACTIVE','DESIGNED AUTOMATICALLY')
        GROUP BY
            Attribute_Name,
            ADAPTERID
        HAVING
            COUNT(DISTINCT(Attribute_Value)) &gt; 1
    ) Mult
    INNER JOIN database1 D ON
        Mult.Attribute_Name = D.Attribute_Name AND
        Mult.ADAPTERID = D.ADAPTERID


This will still display multiple records with the same Attribute_Value if repeated (by Attribute_Name and ADAPTERID), then you can just add DISTINCT right after SELECT to see uniques. You might also want to filter by Attribute_Value on the outmost SELECT in case you are just interested in this values.

SELECT DISTINCT
    D.Attribute_Name,
    D.ADAPTERID,
    D.Attribute_Value
-- ...


Also the INNER JOIN will filter rows that have NULL value on either Attribute_Name or ADAPTERID (on most databases). If you still want to display them you will have to add NULL check functions (the syntax depends on the DBMS, like ISNULL() for SQL Server or NVL() for Oracle).