Link 3 Tables in MySQL

Hello MySql gurus,

I would like to link 3 tables, namely:

  1. People (ID)
  2. Companies (ID)
  3. Suppliers (TYPE, ID)

Rows of Suppliers can come from People or Companies linked by ID. TYPE distinguishes the difference. If TYPE = 0, ID is in People, otherwise, it’s in Companies.

I would like to create a View. If it is not possible in View, can it be solved using a Stored Procedure? How?

Thank you.

you will probably need a union

Select a.Type, b.Name from Suppliers a, People b where a.type = b.ID and a.type = 0 UNION Select x.Type, y.Name from Suppliers x, Companies y where x.type = y.ID and a.type <> 0;

Hi @Jeff Tullin , thank you. I will try this and get back to you with results. Again, loads of thanks! Cheers!

Jeff, I got it. Thank you! Now my minor problem is how to ORDER BY the unioned view (for example, by name).

Select   a.Type, b.Name 
from Suppliers a, People b
where a.type = b.ID and a.type = 0
UNION
Select   x.Type, y.Name 
from Suppliers x, Companies y
where x.type = y.ID and a.type <>  0
ORDER BY b.name