INNER JOIN with multiple tables gives "Syntax error in FROM clause"

Question

I am developing a VB.NET program which has the need to join multiple tables and display them on a DataGridView.

I wrote this query:

SELECT m.idMedicos, m.Nombre, m.Apellido, h.idHistorial, h.CedulaP,
        r.*, rm.*,
        me.Nombre, me.Tipo, me.Administracion
   FROM Medicos m 
     INNER JOIN Historial h ON m.idMedicos = h.idMedicos 
     INNER JOIN Recetas r ON r.idHistorial = h.idHistorial 
     INNER JOIN RecetaMedica rm ON rm.idReceta = r.idReceta
     INNER JOIN Medicamentos me ON me.idMedicamento = rm.idMedicamento 
   WHERE h.CedulaP = $dato

($dato varies with each query)

When I run my program, it fails with the error: "Syntax error in FROM clause".

I tried to add a comma before each INNER, but the error persists.

Note: I am using a MS Access database, and I run all the queries with an OleDbDataAdapter.

Dim Consulta As New OleDb.OleDbDataAdapter("Select m.idMedicos, m.Nombre, m.Apellido, 
             h.idHistorial, h.CedulaP, r.*, rm.*, me.Nombre, me.Tipo, me.Administracion 
             FROM Medicos m INNER JOIN Historial h ON m.idMedicos = h.idMedicos, INNER JOIN Recetas r ON r.idHistorial = h.idHistorial, INNER JOIN RecetaMedica rm ON rm.idReceta = r.idReceta,
             INNER JOIN Medicamentos me ON me.idMedicamento = rm.idMedicamento 
             WHERE h.CedulaP ='" + dato + "'", conexion)

¿Can you guys give me a hand and see what happens here ? :/

Your first query looks good to me. Can you run that in the DB directly and add the detailed error message here — juergen d, Oct 20 '15 at 13:40
Execute the query directly in MySQL and not in your program code. — juergen d, Oct 20 '15 at 13:52
Possible duplicate of [MS Access syntax error in FROM clause](http://stackoverflow.com/questions/21465126/ms-access-syntax-error-in-from-clause) — Gord Thompson, Oct 20 '15 at 14:00
Why do you have commas (,) after each `INNER JOIN` on the second block of code? Try removing the comma at `ON m.idMedicos = h.idMedicos, INNER JOIN Recetas ` — Kyle Williamson, Oct 20 '15 at 14:40

score 0 · Answer 1 · answered Oct 20 '15 at 14:26

0

I think that the problem is that you didn't include the me.idMedicamento in the initial SELECT so it's not recognizing it.

answered Oct 20 '15 at 14:26

Raúl Vivanco García

11
3

added what you said in the select ( and added another detail more ) and still not working .. "Select m.idMedicos, m.Nombre, m.Apellido,h.idMedicos, h.idHistorial, h.CedulaP, r.*, rm.*, me.idMedicamento, me.Nombre, me.Tipo, me.Administracion FROM Medicos m INNER JOIN Historial h ON m.idMedicos = h.idMedicos INNER JOIN Recetas r ON r.idHistorial = h.idHistorial INNER JOIN RecetaMedica rm ON rm.idReceta = r.idReceta INNER JOIN Medicamentos me ON me.idMedicamento = rm.idMedicamento WHERE h.CedulaP ='"+dato+"'" – TwoDent Oct 20 '15 at 14:42

score 0 · Answer 2 · edited May 23 '17 at 12:07

Looking at this answer it would seem that you require to use parentheses to keep the joins logically separated. Here is a good read on the topic.

Your query should look like this when completed correctly:

Dim Consulta As New OleDb.OleDbDataAdapter("Select m.idMedicos, m.Nombre, m.Apellido, h.idHistorial, h.CedulaP, r.*, rm.*, me.Nombre, me.Tipo, me.Administracion FROM (((Medicos m  INNER JOIN Historial h ON m.idMedicos = h.idMedicos )   INNER JOIN Recetas r ON r.idHistorial = h.idHistorial ) INNER JOIN RecetaMedica rm ON rm.idReceta = r.idReceta )    INNER JOIN Medicamentos me ON me.idMedicamento = rm.idMedicamento WHERE h.CedulaP ='" + dato + "'", conexion)

score 0 · Accepted Answer · answered Nov 20 '15 at 16:34

I already found a solution to this problem ( and indeed, long ago . )

I downloaded Fly Speed MySQL Query... It is a program in which you make your tables, relationships and others, that at the end , generates the correct code . (Sometimes you'll need to make some changes , if necessary ) .

http://www.activedbsoft.com/overview-querytool.html

Thanks guys for your help.

INNER JOIN with multiple tables gives "Syntax error in FROM clause"

3 Answers3