SQL Syntax with regards to SELECT

Question

I keep getting an error saying that a specific column, doesn't exist for b.BookCode But I am fully aware that it does exist! I just wanted to make sure after staring at this for so long that I am not missing something obvious.

SELECT AuthorLast, AuthorFirst, OnHand, Title 
FROM (Inventory i, Author a, Book b) 
WHERE (i.BookCode = b.BookCode AND b.AuthorNum = a.AuthorNum);

I'm very new to SQL so I'm unsure if my syntax is off, Also do I need parentheses around the columns that I mentioned in SELECT. I had parentheses around them at first and got an error and was confused as to why.

Thanks!

Answer 1

Minimally, you should change this to:

SELECT AuthorLast, AuthorFirst, OnHand, Title  
FROM Inventory i, Author a, Book b  
WHERE i.BookCode = b.BookCode AND 
      b.AuthorNum = a.AuthorNum; 

... and this is assuming that the columns in the SELECT part of your select statement are not ambiguous (i.e., found in more than one of the tables in the FROM part).

Answer 2

You are using the old join syntax, you should use INNER JOIN instead.

SELECT AuthorLast, AuthorFirst, OnHand, Title
FROM Inventory i
INNER JOIN Author a
    USING (BookCode)    -- You can also use ON, but USING remove the ambiguous columns
INNER JOIN Book b
    USING (AuthorNum);   -- Same thing here

If you get an error saying one column doesn't exist, well, you should double check it. MySQL isn't lying to you for the sake of it! You might do your query on an outdated database, for instance.