MySQL merge

[meghatronic]

I have two tables in mysql that I'd like to easily link about a shared field (uid).

One table has 20 rows (and unique uid), the other has multiple rows referring to the single uid.

e.g.

food table:
type                uid
vegetable         1
alcohol             2
meat                3

item table:
item           uid
wine           2
beer           2
chicken       3

to:

end table:

item table:
item           type
wine           alcohol
beer           alcohol
chicken       meat

I was about to link the tables using php but wondered if there was an easier way?

 

[matja]

SQL was designed to do that, this should work :

select item.item, food.type from item inner join food on item.uid = food.uid;

a conceptually simpler (to some people) method is the cross-join :

select item.item, food.type from item, food where item.uid = food.uid;

same result, but a cross-join is less efficient than an inner join (but you'd never notice the difference with less than 100 rows in each table). You might want to use an outer join instead, which would give the type numbers if they dont exist in the food table, whereas an inner join would not output them at all if they do not exist in the food table.

 

[meghatronic]

I knew it was simple!

Cheers... I did it in phpmyadmin (cross join > output > new table > input) much quicker.