Query help, dynamic number of joins?

,
1

Hi all,

I am new to this forum. Except for basics, I didn't have much of exposure to more advanced SQL queries. Hence I'm seeking any advice that can help me understand better the following problem.

Let's say I have two tables (A and B). The primary keys are A.id and B.id. In addition, table B has two more fields, B.a_key (foreign key for A.id) and B.feature. Therefore, table B may(usually) have more entries since multiple rows in B may be pointing to a single A.id.

I have a query that is format:

  1. [{B.feature=1}, {B.feature=0}, {B.feature=3}]
    Return ALL A.id that have at least 3 entries in table B with values (1, 0, 3)

  2. [{B.feature=0}]
    Return ALL A.id that have at least 1 entry in B with value (0)

  3. [{B.feature=0}, {B.feature=2}, {B.feature=5}, {B.feature=6}]
    Return ALL A.id that have at least 4 entries in table B with values (0,2,5,6)
    ....

I was thinking of preparing an SQL statement with as many JOINS as B entries and then filtering them in the where clause, but I think I'm catching more than needed.
E.g sample 1:

select distinct A.id 
from A
left join B b   on A.id=b.a_key 
left join B bb  on A.id=bb.a_key
left join B bbb on A.id=bbb.a_key
where (b.feature=1 and bb.feature=0 and bbb.feature=3);
2

You should really post consumable test data. As you only seem to want a.id there is no point in joining to table A as you can use b.a_key.
Consumable test data:

CREATE TABLE #b
(
	id int NOT NULL PRIMARY KEY
	,a_key int NOT NULL
	,features varchar(255) NOT NULL
);
INSERT INTO #b
VALUES (1, 1, '[{B.feature=1}, {B.feature=0}, {B.feature=3}]')
	,(2, 1, '[{B.feature=0}]')
	,(3, 1, '[{B.feature=0}, {B.feature=2}, {B.feature=5}, {B.feature=6}]');

As your features strings are nearly JSON, make them JSON and then get the values:

SELECT B.id, B.a_key, J.feature
FROM #b B
	CROSS APPLY
	(
		VALUES
		(
			REPLACE(B.features, 'B.feature=', '"feature":')
		)
	) X (features)
	CROSS APPLY OPENJSON(X.features) WITH (feature int '$.feature') J;

The results you want are not clear but you should then be able to use GROUP BY.
Maybe something like the following for option 2:

SELECT B.a_key
FROM #b B
	CROSS APPLY
	(
		VALUES
		(
			REPLACE(B.features, 'B.feature=', '"feature":')
		)
	) X (features)
	CROSS APPLY OPENJSON(X.features) WITH (feature int '$.feature') J
WHERE J.feature = 0
GROUP BY B.a_key
HAVING (COUNT(1)) > 0;

If you want a better answer, post consumable test data for all 3 options and the results you would expect from the consumable test data.

3 

select A.id 
from A
inner join B b on A.id=b.a_key 
group by A.id
HAVING 
    MAX(CASE WHEN b.feature = 0 THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN b.feature = 1 THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN b.feature = 3 THEN 1 ELSE 0 END) = 1

...
HAVING 
    MAX(CASE WHEN b.feature = 0 THEN 1 ELSE 0 END) = 1

...
HAVING 
    MAX(CASE WHEN b.feature = 0 THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN b.feature = 2 THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN b.feature = 5 THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN b.feature = 6 THEN 1 ELSE 0 END) = 1