CODEWITHSUNDEEP
mysql
spiridon
spiridon

Reputation: 323

Find results from another table based on a conditional row

I have two tables, one is a list of families who have pets for adoption, the other is the list of potential adopters.

Some familes are only giving away all of their pets at once (all), but some are giving them away separately (one). This is indicated in the "how_many" row.

The adopters have preferences considering the animals, some would adopt only a specific one, other would adopt more than one.

What I'd like to achieve is to write a single query which connects the families with the potential adopters.

So if there's a family who would give away a cat and a bird (not separately), e.g. the Williams family (ID 3, all) they would be connected with User 4, who would like adopt a cat and a bird (besides a dog, but that doesn't matter, not all 3 have to match, just a subset).

Or, if there's a family who would give away their pets separately, e.g. the Smith family (ID 1, one), they would be connected with all those adopters who would like to adopt a bird or a cat, like User 1, User 2, etc.

Is there a way to achieve this in one query?

Families

id  animal  family      how_many

1   bird    Smith       one
1   cat     Smith       one
2   bird    Johnson     one
2   dog     Johnson     one
3   cat     Williams    all      
3   bird    Williams    all
4   bird    Brown       one
5   cat     Jones       all
6   bird    Miller      all
7   bird    Davis       one
7   cat     Garcia      one
7   bird    Garcia      one
7   dog     Garcia      one


Adopters

id  animal  adopter
1   cat     User 1
1   bird    User 1
2   bird    User 2
2   dog     User 2
3   bird    User 3
3   dog     User 4
4   cat     User 4
4   dog     User 4
4   bird    User 4
5   bird    User 5
6   bird    User 6

Upvotes: 2

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Hmmm . . . I think the most reasonable approach is to combine two queries, one for the "one"s and one for the "all"s:

select f.*, a.id
from families f join
     adopters a
     on f.animal = a.animal 
where f.how_many = 'one'
union all
select f.id, group_concat(f.animal), f.family, f.how_many, a.id
from families f left join
     adopters a
     on f.animal = a.animal 
where f.how_many = 'all'
group by f.id, f.family, f.how_many, a.id
having count(*) = count(a.animal);

Upvotes: 1

Related Questions