brunobliss brunobliss - 16 days ago 4
MySQL Question

mysql replacing column of id's with names referenced in another table

i have two tables, suppliers and orders.

suppliers table

id | name
-------------
1 | dell
2 | lexmark
3 | xerox


orders table

id_supplier | date
--------------------------
2 | 2016-01-01
2 | 2016-01-05
1 | 2016-01-06


Was wondering if i can make a select statement that substitutes the id_supplier by it's name (referenced on the suppliers table) so i'd end up with something like this:

name_supplier | date
--------------------------
lexmark | 2016-01-01
lexmark | 2016-01-05
dell | 2016-01-06


Would like to know if this is the correct aproach or if i should simply insert it's name in the orders table. Thanks in advance!

Answer

A simple join is what you need

SELECT name as name_supplier, `date` FROM suppliers INNER JOIN orders on suppliers.id = orders.id_supplier

Would like to know if this is the correct aproach Yes definitely, it's the correct approach

or if i should simply insert it's name in the orders table. nopes, you should not have repeating data like that. Please refer: https://en.wikipedia.org/wiki/Database_normalization