3 Table Database on MySQL

Question

Is it possible to join 3 tables on MySQL on which final output will show sample below.

Table1

Dog     2
Cat     3

Table2

Dog     1
Cow     3

Table3

Cat     1
Rat     3

Final Output

Dog     3
Cat     4
Rat     3
Cow     3

Updated Output

Thanks, but how to do if the output is like below.<br><table>
  <tr>
    <th>Animal</th>
    <th>Table 1</th><th>Table 2</th><th>Table 3</th>
  </tr>
  <tr>
    <td>Dog</td>
    <td>2</td><td>1</td><td>0</td>
  </tr>  <tr>
    <td>Cat</td>
    <td>3</td><td>0</td><td>1</td>
  </tr><tr>
    <td>Cow</td>
    <td>0</td><td>3</td><td>0</td>
  </tr>  <tr>
    <td>Rat</td>
    <td>0</td><td>0</td><td>3</td>
  </tr></table>

I hope you can help me with this problem.

Thanks.

Use UNION ALL and then aggregate the result on the animal column. This isn't a join problem. — danblack
– danblack, Commented Mar 1, 2019 at 4:46
@danblack thanks, do you know also how to update data on output when new data is updated on any of the tables, i mean the most efficient way not just using timer or delay to fetch data. — Raven
– Raven, Commented Mar 1, 2019 at 4:50
Nothing sane. SQL isn't designed as an event service. Is this query slow? Why do you have three tables having the same data anyway? — danblack
– danblack, Commented Mar 1, 2019 at 4:53
@danblack i just want an efficient way of retrieving data from the database not using interval, time or delay loop. Page will just update data if there is a changes on the data on the tables and not fetch data like every 30 seconds. — Raven
– Raven, Commented Mar 1, 2019 at 5:00
Its a new question, requires information on how up to date you want things, how is data populated, why you have 3 tables etc. — danblack
– danblack, Commented Mar 1, 2019 at 6:10

Tony DeStefano · Accepted Answer · 2019-03-01 17:46:27Z

2

You can use a union statement to merge the three tables together and then select from them together like this:

SELECT
    column1,
    SUM(column2)
FROM
(
    SELECT column1, column2 FROM Table1
    UNION ALL
    SELECT column1, column2 FROM Table2
    UNION ALL
    SELECT column2, column2 FROM Table3
)
GROUP BY column1

If your column names are different across the three tables, you would need to alias the column names.

edited Mar 1, 2019 at 17:46

answered Mar 1, 2019 at 4:48

Tony DeStefano

8296 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tony DeStefano Over a year ago

@Strawberry thanks! I updated my answer. That makes much more sense.

Collectives™ on Stack Overflow

3 Table Database on MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related