Mysql - Common sql for a SELECT-query by specific values from the second table

DStarling
May 21, 2023
310 views
3 votes
3 Answers

I have 2 tables, which represent one to many relationship

CREATE TABLE a( id int, a_text varchar(255) );

And

CREATE TABLE b( a_id int, b_text varchar(255) );

Where column b_text contains enum values.

Table A:

Id	a_text
1	‘text 1’
2	‘text 2’

Table B:

a_id	b_text
1	‘status 1’
1	‘status 2’
2	‘status 1’
1	‘status 3’

I want to write a SELECT query with several b_text values as parameters, that returns all rows from table a, that contain only given b_text values after join. For example, I want to get all rows from table a that have only b_text values status_1 and status_2. So row with id=1 will be returned.

The question is:
Is there a way to write a common sql query, that does not depend of number of parameters?

I have a sollution, in which i just add new JOIN to b table with where statement for a speciffic value, but it doesn’t suit because there is no way to dynamically add a new join

Answers

- RolandHolzer
- May 21, 2023 at 7:33 pm
- 0 votes
0
You can use intersect:
```
Select * from a inner join b on a.id=b.a_id where status = 'status_1'
intersect 
Select * from a inner join b on a.id=b.a_id where status = 'status_2'
```
OR you can use in:
```
Select * from a where 
id in 
(select a_id from b where status='status_1')
and id in 
(select a_id from b where status='status_2')
```
Login or Signup to reply.

- TommasoGagliardi
- May 21, 2023 at 9:58 pm
- 0 votes
0
If you are looking to match certain values between the two tables have you already tried a Left Join?

This will return only b_text with the values you indicated:
```
SELECT id, a_text, b_text
FROM a LEFT JOIN b
ON a.id = b.a_id
WHERE b_text like '%1' OR b_text like '%2';
```
Login or Signup to reply.

- ThorstenKettner
- May 21, 2023 at 10:51 pm
- 0 votes
0
The straight-forward way to write the query is:
```
select *
from a
where id in (select a_id from b where b_text = 'status_1')
  and id in (select a_id from b where b_text = 'status_2')
order by id;
```
This means for every status you’ll have to add a condition with AND.

If you want a query that does not change with the number of conditions, then the desired statuses must be stored in a separate table. The query could look like this then for instance:
```
select *
from a
where not exists
(
  select null
  from desired
  where desired.b_text not in
  (
    select b.b_text
    from b 
    where b.a_id = a.id
  )
)
order by id;
```
Or, instead of a separate table you use a comma-separated list (e.g. 'status 1,status 2') along with FIND_IN_SET.
```
set @desired = 'status 1,status 2';

select *
from a
where id in
(
  select a_id
  from b
  where find_in_set (b_text, @desired)
  group by a_id
  having count(*) = (select char_length(@desired) - 
                            char_length(replace(@desired, ',', '')) + 1)
);
```
Demo: https://dbfiddle.uk/0dC-PbEQ
Login or Signup to reply.