SELECT from two MySQL tables

Question

I have the following MySQL tables:

tbl_pet_owners:

+----+--------+----------+--------+--------------+
| id | name   | pet      | city   | date_adopted |
+----+--------+----------+--------+--------------+
|  1 | jane   | cat      | Boston | 2017-07-11   |
|  2 | jane   | dog      | Boston | 2017-07-11   |
|  3 | jane   | cat      | Boston | 2017-06-11   |
|  4 | jack   | cat      | Boston | 2016-07-11   |
|  5 | jim    | snake    | Boston | 2017-07-11   |
|  6 | jim    | goldfish | Boston | 2017-07-11   |
|  7 | joseph | cat      | NYC    | 2016-07-11   |
|  8 | sam    | cat      | NYC    | 2017-07-11   |
|  9 | drew   | dog      | NYC    | 2016-07-11   |
+----+--------+----------+--------+--------------+

tbl_pet_types:

+----------+-------------+
| pet      | type        |
+----------+-------------+
| cat      | mammal      |
| dog      | mammal      |
| goldfish | fish        |
| goldfish | seacreature |
| snake    | reptile     |
+----------+-------------+

Here's the SQL command I want to construct, in English:

Select name, pet, and pet's type where owner's city is Boston. Additionally, no duplicates are allowed in the results set. The result would be:

+------+----------+-------------------+
| name | pet      | type              |
+======+==========+===================+
| jane | cat      | mammal            |
+------+----------+-------------------+
| jane | dog      | mammal            |
+------+----------+-------------------+
| jack | cat      | mammal            |
+------+----------+-------------------+
| jim  | snake    | reptile           |
+------+----------+-------------------+
| jim  | goldfish | fish, seacreature |
+------+----------+-------------------+

This is what I have so far:

SELECT result FROM (
SELECT DISTINCT owners.name, owners.pet, owners.city,
group_concat(DISTINCT types.type separator ', ') AS type
FROM tbl_pet_owners owners
INNER JOIN tbl_pet_types types ON owners.pet = types.pet
GROUP BY owners.name, owners.pet )
as result WHERE result.city = 'Boston'

but I'm getting error: unknown column 'result' in 'field list'

Answer 1

I've not got a mysql instance handy but I think this is close to what you need:

SELECT tpo.name,
       tpo.pet,
       GROUP_CONCAT(DISTINCT tpt.type separator ', ') AS type
FROM tbl_pet_owners tpo
INNER JOIN tbl_pet_types tpt ON tpt.pet = tpo.pet AND tpo.city = 'Boston'
GROUP BY tpo.name,
         tpo.pet;

EDIT I've put this together in SQL Fiddle

http://sqlfiddle.com/#!9/e902e/1/0

Answer 2

There are generally two approaches:

1. Join the tables and then aggregate them somehow in such a way to get distinct pets from the first table and type lists of the second.
2. Get distinct pets from the first table, get type lists from the second, then join.

I find the second approach much better, as you only join what you want to join (distinct pets with type lists). The query is:

select
  pet_owners.name, 
  pet_owners.pet, 
  pet_types.types
from 
(
  select distinct name, pet
  from tbl_pet_owners
  where city = 'Boston'
) pet_owners
join
(
  select pet, group_concat(type) as types
  from tbl_pet_types
  group by pet
) pet_types on pet_types.pet = pet_owners.pet;

The join-first-muddle-through query looks much simpler and works as well:

select 
  po.name,
  po.pet,
  group_concat(distinct pt.type) as types
from tbl_pet_owners po
join tbl_pet_types pt on pt.pet = po.pet
where po.city = 'Boston'
group by po.name, po.pet;

Both tables are aggregated (one via DISTINCT, one via GROUP BY) and this works just fine. There are other cases however, when you need joined aggregates from two tables and this approach fails (typical: multiplied counts). So aggregating before joining is a good habit to stick to.