getting data from more than one table

[bodycount]

I have three tables

Personal----(table 1's name)
ID
name
address
town
country

Hobbies----(table 2's name)
name_ID
hobby

Food----(table 3's name)
name_ID
food

if i run the following query that works fine.

select name,food from personal,food where personal.ID=name_ID;

result

+-----------+--------+
¦ name ¦ food ¦
+-----------+--------+
¦ John Dunn ¦ Currie ¦
+-----------+--------+

if i try and get data from all three tables using the following query

select name,food,hobby from personal,food,hobbies where personal.ID=name_ID;

I get the following error

ERROR 1052 (23000): Column 'name_ID' in where clause is ambiguous

what am i doing wrong

[eelixduppy]

Hello

I believe you want to do something like...

select name,food,hobby from personal,food,hobbies where personal.ID=food.name_ID;

Because it doesn't know which name_ID you are referring to

[bodycount]

Thanks for that

The way i have linked my tables is that the right way of doing it?. Should i of used name_ID in both tables?

[eelixduppy]

Yes, you are doing it correctly. The only reason that in your first query you didn't get an error was because there was only one column labeled name_ID, so it knew which one you were referring to.

[hakre]

next to the cross-table-select which would in general produce a resultset you can use, a so called JOIN can be done which lets you control the way how the two tables are combined.

for that i strongly advice to read and understand the docs:

[dev.mysql.com...]

i know it's not an easy topic at all but you run into joins quite fast while using multiple tables.