Only SELECT rows from table when two rows from the same table fulfill the condition as a whole under the same ID

Question

Title is a bit weird, but I am not quite sure how to formulate this problem quite right. Maybe this is why I cannot find the solution...?

I am currently crawling through a SQLite database which has the following format:

filename	id	name	value
orig_db_1.db	1	A_USERNAME_VAR	user
orig_db_1.db	1	A_PASSWORD_VAR	password
orig_db_1.db	1	SOME_OTHER_VAR	helloworld
orig_db_1.db	2	A_USERNAME_VAR	user
orig_db_1.db	3	A_PASS_VAR	password
orig_db_2.db	12	A_USER_VAR	user
orig_db_2.db	12	A_PASS_VAR	password
orig_db_2.db	12	SOMETHING_DIFFERENT	12345
orig_db_3.db	42	IDK_WHAT_TO_TYPE_HERE	idksomething

Intuitively, let's say the goal is to find out if someone has exposed their full logins here. This means for the same filename and ID, someone has both defined a username variable, and a password variable, which is bad and we want to analyze more closely by looking at the rest of the rows and trying to identify which piece of software this is.

In SQL terms, my goal is to find a SQL query, which returns all rows for a combination of filename + ID, if under this combination there is one row with a name containing "USER" (LIKE '%USER%'), and a row with a name containing "PASS" (LIKE '%PASS%').

For the same example, this is what I want as a result:

filename	id	name	value
orig_db_1.db	1	A_USERNAME_VAR	user
orig_db_1.db	1	A_PASSWORD_VAR	password
orig_db_1.db	1	SOME_OTHER_VAR	helloworld
orig_db_2.db	12	A_USER_VAR	user
orig_db_2.db	12	A_PASS_VAR	password
orig_db_2.db	12	SOMETHING_DIFFERENT	12345

How could I do this in a SQLite compatible query?

Answer 1

1. Find id+filename which have passwords
2. Find id+filename which have usernames
3. Inner join with the original table to filter out those rows that have no corresponding values in passwordss and usernames

Something along these lines:

with passwords as (
  select filename, id from files
  where name like '%PASS%'
  group by filename, id
),
usernames as (
  select filename, id from files
  where name like '%USER%'
  group by filename, id
)
select files.* from files
inner join passwords using(filename, id)
inner join usernames using(filename, id);

Answer 2

In modern versions of SQLite 3.25+, you can do this very efficiently with window functions. No self-joins needed.

SELECT
  filename,
  id,
  name,
  value
FROM (
    SELECT *,
      COUNT(CASE WHEN name LIKE '%USER%' THEN 1 END) OVER (PARTITION BY id) usr,
      COUNT(CASE WHEN name LIKE '%PASS%' THEN 1 END) OVER (PARTITION BY id) pass
    FROM files
) f
WHERE usr > 0
  AND pass > 0;

db<>fiddle