CODEWITHSUNDEEP
mysql
Gerrit de Beer
Gerrit de Beer

Reputation: 35

How to fix unknown table for a table that is in the database?

I am getting this error:

Error 1051: Unknown Table 'employee'

This is my code:

 CREATE DEFINER=`u24290718`@`%` PROCEDURE `eval5_get_charter_details_by_pilot_date`(in fromdate DATE, todate DATE, pilotname VARCHAR(50))
BEGIN

    if (pilotname = 'all')
    THEN
        SELECT aviaco.pilot.*, aviaco.employee.*

        FROM aviaco.pilot

        JOIN aviaco.employee AS emp on aviaco.pilot.EMP_NUM = aviaco.employee.EMP_NUM;

    ELSE
        SELECT aviaco.pilot.*, aviaco.employee.*

        FROM aviaco.pilot

        JOIN aviaco.employee AS emp on aviaco.pilot.EMP_NUM = aviaco.employee.EMP_NUM

        WHERE aviaco.pilot.PIL_MED_DATE = fromdate & aviaco.pilot.PIL_PT135_DATE = todate;
    END IF;
END

So the problem is that I keep getting unknown table employee. I am using a database on the university server and the employee table is there! I have no idea what I am doing wrong.

Upvotes: 0

Views: 2642

Answers (1)

Nanne
Nanne

Reputation: 64409

I believe this is your issue:

aviaco.employee AS emp

so your query (not your database) now knows your employee table as emp. This means you should point to it as such, so for instance (there might be more places!), you can't do this

SELECT aviaco.pilot.*, aviaco.employee.*

but should do

SELECT aviaco.pilot.*, emp.*

and in the join it should be emp.EMP_NUM etc.

Upvotes: 1

Related Questions