Trouble with select statement on three table join

Question

Im trying to write a query that will return a set of columns from three different tables.

The table that is link between the other two tables is called Table_A, it contains the keys of for the other two tables. The second table is called the Table_B and the last table is called the Table_C.

Table_A columns.

| a_ID (primary key)| b_ID (foreign key)| c_ID (foreign key)| ..... |

Table_B columns

|b_ID (primary key)| b1 | b2 | ...... |

Table_C columns

| c_ID (primary key) | c1 | c2 | ...... |

This is my SQL Query below. (Im only concerned with the columns above although there are more in each table.)

SELECT b.b_ID
     , b.b1
     , b.b2
     , a.a_ID
     , c.c1
     , c.c2 
FROM Table_A AS a 
    JOIN Table_B AS b ON a.b_ID = b.b_ID
    JOIN Table_C AS c ON a.c_ID = c.c_ID

Im using open office for my projects and the error I'm getting is

"Table not found in statement [ SELECT b.b_ID , b.b1 , b.b2 , a.a_ID , c.c1 , c.c2 FROM Table_A AS a JOIN Table_B AS b ON a.b_ID = b.b_ID JOIN Table_C AS c ON a.c_ID = c.c_ID]"

For some reason if I change the select statement just to get all columns (*) it returns the correct results but I need to narrow it down to the columns listed in my query.*

SELECT *
FROM Table_A AS a 
    JOIN Table_B AS b ON a.b_ID = b.b_ID
    JOIN Table_C AS c ON a.c_ID = c.c_ID'

EDIT: I have removed the actual table and column names so that you don't have to understand the story to help with the issue.

Could you post the working versions of the query? The one that runs with * ? — Andomar
SELECT * FROM registrationTbl AS a JOIN EventTbl AS b ON a.eventID = b.eventID JOIN AttendeeTbl AS c ON a.attendeeID = c.AttendeeID WHERE b.startDate > '2013-10-01' — gazma
are the column names repeated in the three tables? If you ran the query without the table prefix does it still error? — John Kane
Your RDBMS is open office? Can you be more specific about that? — Cyril Gandon

Unknown Unknown · Accepted Answer · 2013-04-29T12:21:55

Shouldn't your WHERE clause be:

WHERE b.eventStartDate > '2013-10-01'

?

This is a "spot the difference" type of question...

Trouble with select statement on three table join

1 Answers