AfNJ9Z69

1GROUP BY id
2HAVING COUNT(DISTINCT column_name) = count_of_list_values; 
3     
4SELECT
5        am_software_archive.asset_name
6    FROM
7        am_software_archive
8    WHERE
9        LOWER(am_software_archive.sw_name) IN('nodejs', 'visio 2013')
10    GROUP BY
11        am_software_archive.id
12    HAVING
13        COUNT(
14            DISTINCT am_software_archive.sw_name
15        ) = 2 
16     
17CREATE TABLE IF NOT EXISTS am_software_archive(
18                    id BIGINT NOT NULL UNIQUE,
19                    asset_name VARCHAR(10) NOT NULL,
20                    sw_name VARCHAR(150) NOT NULL,
21                    sw_developer BIGINT NOT NULL,
22                    sw_key VARCHAR(50) DEFAULT NULL,
23                    sw_osver VARCHAR(15) DEFAULT NULL,
24                    CONSTRAINT PK_software PRIMARY KEY(id, asset_name),
25                    INDEX idx_sw_name_asset(asset_name,sw_name),
26                    INDEX idx_sw_key_asset_name(asset_name,sw_key),
27                    INDEX idx_sw_name_sw_key(sw_name,sw_key),
28                    INDEX idx_osver_sw_name(sw_name,sw_osver),
29                    INDEX idx_osver_asset_name(asset_name,sw_osver)
30                )