Combine data from three unrelated tables

Question

I need to use a data visualization tool that can only query a single source for a given chart. I have three tables with the data I need to visualize. So, I need to combine them into a single view or output table. Here are the table schemas:

MySQL [bdCaloriesNeeded]> desc activity;
+---------------+----------+------+-----+---------+-------+
| Field         | Type     | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| id            | int(11)  | YES  |     | NULL    |       |
| name          | text     | YES  |     | NULL    |       |
| Gender        | text     | YES  |     | NULL    |       |
| age           | int(11)  | YES  |     | NULL    |       |
| length        | text     | YES  |     | NULL    |       |
| weight        | int(11)  | YES  |     | NULL    |       |
| exercise      | int(11)  | YES  |     | NULL    |       |
| food_consumed | int(11)  | YES  |     | NULL    |       |
| date          | datetime | YES  |     | NULL    |       |
+---------------+----------+------+-----+---------+-------+

MySQL [bdCaloriesNeeded]> desc exercise;
+---------------------+---------+------+-----+---------+-------+
| Field               | Type    | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| Gender              | text    | YES  |     | NULL    |       |
| Min_Age             | int(11) | YES  |     | NULL    |       |
| Max_Age             | int(11) | YES  |     | NULL    |       |
| min_exercise_hours  | int(11) | YES  |     | NULL    |       |
| med_exercise_hours  | int(11) | YES  |     | NULL    |       |
| high_exercise_hours | int(11) | YES  |     | NULL    |       |
+---------------------+---------+------+-----+---------+-------+

MySQL [bdCaloriesNeeded]> desc food;
+---------------------+---------+------+-----+---------+-------+
| Field               | Type    | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| size                | text    | YES  |     | NULL    |       |
| min_pounds          | int(11) | YES  |     | NULL    |       |
| max_pounds          | int(11) | YES  |     | NULL    |       |
| min_food_oz_per_day | int(11) | YES  |     | NULL    |       |
| max_food_oz_per_day | int(11) | YES  |     | NULL    |       |
+---------------------+---------+------+-----+---------+-------+

Here's the actual source data in the above tables:

MySQL [bdCaloriesNeeded]> select * from activity;
+------+----------+--------+------+--------+--------+----------+---------------+---------------------+
| id   | name     | Gender | age  | length | weight | exercise | food_consumed | date                |
+------+----------+--------+------+--------+--------+----------+---------------+---------------------+
|   14 | spot     | M      |    2 | 2'7"   |     13 |        5 |            13 | 2017-10-08 00:00:00 |
|   67 | princess | F      |    6 | 3'3"   |     75 |        3 |            15 | 2017-09-05 00:00:00 |
+------+----------+--------+------+--------+--------+----------+---------------+---------------------+

MySQL [bdCaloriesNeeded]> select * from exercise
+--------+---------+---------+--------------------+--------------------+---------------------+
| Gender | Min_Age | Max_Age | min_exercise_hours | med_exercise_hours | high_exercise_hours |
+--------+---------+---------+--------------------+--------------------+---------------------+
| M      |       1 |       2 |                  1 |                  4 |                   6 |
| M      |       3 |       7 |                  1 |                  3 |                   4 |
| M      |       8 |      15 |                  1 |                  2 |                   2 |
| F      |       1 |       2 |                  1 |                  4 |                   6 |
| F      |       3 |       7 |                  1 |                  3 |                   5 |
| F      |       8 |      15 |                  1 |                  2 |                   2 |
+--------+---------+---------+--------------------+--------------------+---------------------+

MySQL [bdCaloriesNeeded]> select * from food;
+--------+------------+------------+---------------------+---------------------+
| size   | min_pounds | max_pounds | min_food_oz_per_day | max_food_oz_per_day |
+--------+------------+------------+---------------------+---------------------+
| small  |          1 |         10 |                  12 |                  18 |
| medium |         11 |         30 |                  15 |                  30 |
| large  |         31 |        100 |                  25 |                  50 |
+--------+------------+------------+---------------------+---------------------+

Here's the SQL I'm executing:

SELECT activity.id, activity.name, activity.Gender, activity.age, activity.weight, activity.exercise, activity.date, exercise.min_exercise_hours, exercise.high_exercise_hours, food.size, food.min_food_oz_per_day, food.max_food_oz_per_day
from activity, exercise, food
where (
activity.exercise between exercise.min_exercise_hours and exercise.high_exercise_hours
)
and
(
activity.weight between food.min_pounds and food.max_pounds
)
and
(
activity.Gender = exercise.Gender
)

Here's the undesired result I'm getting:

+------+----------+--------+------+--------+----------+---------------------+--------------------+---------------------+--------+---------------------+---------------------+
| id   | name     | Gender | age  | weight | exercise | date                | min_exercise_hours | high_exercise_hours | size   | min_food_oz_per_day | max_food_oz_per_day |
+------+----------+--------+------+--------+----------+---------------------+--------------------+---------------------+--------+---------------------+---------------------+
|   14 | spot     | M      |    2 |     13 |        5 | 2017-10-08 00:00:00 |                  1 |                   6 | medium |                  15 |                  30 |
|   67 | princess | F      |    6 |     75 |        3 | 2017-09-05 00:00:00 |                  1 |                   6 | large  |                  25 |                  50 |
|   67 | princess | F      |    6 |     75 |        3 | 2017-09-05 00:00:00 |                  1 |                   5 | large  |                  25 |                  50 |
+------+----------+--------+------+--------+----------+---------------------+--------------------+---------------------+--------+---------------------+---------------------+

I'm getting two rows for Princess. I need one row for each dog. The desired result should use Princess's's weight to look up the correct range of food per day, and use her gender and age to look up the correct range of exercise.

I've been banging on this for hours, can't see what doing wrong here.

Should have mentioned - the end goal here is to produce visual reports that I can use to show if a pet is getting too much food, too little exercise, or just enough, etc. — S.Patt, Mar 07 '18 at 20:50
Please don't ask people to click a dropbox link. Your table definitions should be *here*, in your question. And not as a picture. Please show what you've tried, where you're stuck, etc. Otherwise, it's literally "click this link, look at my schema, and write my code" - which isn't how things work here. You've already taken the tour (you earned the "informed badge"), so you should have seen all of the info on proper questions. — David Makogon, Mar 07 '18 at 20:52
" I imagine this will involve a BETWEEN operator?" - Don't just imagine... — nicomp, Mar 07 '18 at 20:52
Sorry, first time, thought that would be acceptable. I'll reformat. — S.Patt, Mar 07 '18 at 20:57

Jeff Martin · Accepted Answer · 2018-03-08T15:52:11.130

So interestingly your question says that the tables are Unrelated but they are actually related and this is the whole point of a relational database, to join data based on those relationships.

The issue is that your exercise table is only being joined on exercise hours using the between so princess matches rows 4 and 5 in the exercise table. (the first where clause matches rows 1 and 2 also but the later where clause limits the Gender)

It looks to me like you should also limit the match on the exercise table to age as well as exercise and gender

so add

and (activity.age between exercise.min_age and exercise.max_age)

Also personally i like to use JOIN clauses rather than WHERE - it keeps all the stuff together.

SELECT activity.id,
       activity.name,
       activity.Gender,
       activity.age,
       activity.weight,
       activity.exercise,
       activity.date,
       exercise.min_exercise_hours,
       exercise.high_exercise_hours,
       food.size,
       food.min_food_oz_per_day,
       food.max_food_oz_per_day
FROM activity
  JOIN exercise
    ON activity.exercise BETWEEN exercise.min_exercise_hours AND exercise.high_exercise_hours 
   AND activity.Gender = exercise.Gender
   AND activity.age BETWEEN exercise.min_age AND exercise.max_age
  JOIN food
    ON activity.weight BETWEEN food.min_pounds AND food.max_pounds

Since you are looking for things that may be OUTSIDE of the ranges suggested you may want to consider LEFT JOIN on the exercise and food tables, so that the dogs on the activity table that fall outside of any range will still show up (with NULL values for the missing data for the other table.) just change the join lines to LEFT JOIN like so:

LEFT JOIN exercise
LEFT JOIN food

See also: What is the difference between "INNER JOIN" and "OUTER JOIN"?

Combine data from three unrelated tables

1 Answers1