[NecroWinter]

Hi, im trying to get information from multiple tables, and I have next to no experience with SQL. Ill show you what the database looks like, then ill explain what im trying to do to solve it, then hopefully you can help, I also did not design this database.

theres three tables, book_info, book_title, author

in book_info, you get author_id and book_id

in book_title, you get book_id and a field called title

in author, you get the author_id and the authors name

What I want to do is look in book_info, get author_id and book_id and display the book name and the author

Either a join or a union sounds right, unfortunately, I cant get much to work?

SELECT book_id, author_id
FROM book_information
INNER JOIN author
ON book_information.author_id=author.author_id;

This is just me trying to get something to work, right now it says author_id is ambiguous, this sql query is not really what im looking for in the end, so if you can lead me into what I said Im trying to do, that would be helpful too. I dont know how joining three tables works, the tutorials im looking at only show two tables.

This post has been edited by NecroWinter: 22 September 2012 - 06:08 PM

[exiles.prx]

When you are explicitly listing what data fields you want and the same field name exists in 2 or more tables, you need to explicitly tell mysql what table the data field is coming from. For example using your SQL statement from above:

SELECT book_id, author.author_id
FROM book_information
INNER JOIN author
ON book_information.author_id=author.author_id;

Notice the slight change of author_id to author.author_id (you could even use book_title.author_id). For future reference, you get this error because mysql doesn't know which data field you want (since there are a total of 2 fields named author_id when using JOIN) so you have to tell mysql which table you want the data field from. (hope this makes sense)

Basically to get the data you are looking for, your going to have to join book_info and book_title on book_id and book_title and author on author_id (this can be done using one SQL statement. hint: use 2 JOIN's and 2 ON's).

This post has been edited by exiles.prx: 22 September 2012 - 11:11 PM