I've created an database with 5 tables and each of the tables have various rows and columns as data.

When I try to select some of that data, I'm getting that error in the title and I have no idea how to do it otherwise!!

Here's the thing:

Tables_in_table01
Afdeling
Apparaat
Leverancier
Medewerkers
Onderhoud

Those are the tables my database consist of and these are the definitions:

Afdeling =      AfdelingNR + AfdelingNaam + Medewerkers_MedewerkerNR 

Leverancier =   LeverancierNR + Naam + Contactpersoon + Adres + Telefoon + Email

Apparaat =      ApparaatNR + AfdelingNR + Aanschafdatum + Leverancier_LeverancierNR + Onderhoudtermijn + Afschrijvingsdatum

Medewerkers =   MedewerkerNR + Geslacht + Functie + Voorletters + Tussenvoegsel + Achternaam + Beeper

Onderhoud =     OnderhoudNR + Medewerker_MedewerkerNR + Apparaat_ApparaatNR

When I try to select the following:

SELECT MedewerkerNR, Voorletters, Tussenvoegsel, Achternaam
FROM Medewerkers
WHERE Medewerkers.MedewerkerNR=Afdeling.Medewerkers_MedewerkerNR
AND Afdeling.AfdelingNR=Apparaat.AfdelingNR;

It says:

Error 1054 (42S22): Unknown column 'Afdeling.Medewerkers_MedwerkerNR' in 'where clause'

Why is this column unknown? I've checked it and the data does exist in the table..

After using SHOW CREATE TABLE Afdeling; I've gotten the following:

mysql> SHOW CREATE TABLE Afdeling;
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------+
| Table    | Create Table


                                                      |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------+
| Afdeling | CREATE TABLE `afdeling` (
  `AfdelingNR` varchar(8) NOT NULL,
  `AfdelingNaam` varchar(45) DEFAULT NULL,
  `Medewerkers_MedewerkerNR` decimal(8,0) NOT NULL,
  PRIMARY KEY (`AfdelingNR`),
  KEY `Afdeling_FKIndex1` (`Medewerkers_MedewerkerNR`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------+
1 row in set (0.03 sec)

Any help is greatly accepted!

Cheers!

closed as off-topic by Paul White, RLF, RolandoMySQLDBA, Colin 't Hart, Philᵀᴹ May 1 '15 at 9:23

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, RLF, RolandoMySQLDBA, Colin 't Hart, Philᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

up vote 1 down vote accepted

You only included the Medewerkers table in your query, and not the Afdeling or Apparaat table.

try this:

SELECT MedewerkerNR, Voorletters, Tussenvoegsel, Achternaam
FROM Medewerkers, Afdeling, Apparaat
WHERE Medewerkers.MedewerkerNR=Afdeling.Medewerkers_MedewerkerNR
AND Afdeling.AfdelingNR=Apparaat.AfdelingNR;

or better yet, try this:

SELECT MedewerkerNR, Voorletters, Tussenvoegsel, Achternaam
FROM Medewerkers
INNER JOIN 
Afdeling
ON Medewerkers.MedewerkerNR=Afdeling.Medewerkers_MedewerkerNR
INNER JOIN Apparaat    
ON Afdeling.AfdelingNR=Apparaat.AfdelingNR;
  • 1
    Oh god.. Thanks alot for this.. I should've known this >_> Guess it's time for a break! – Rowan Apr 30 '15 at 14:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.