mySQL query - #1054 - Unknown column 3

Question

Possible Duplicate:
MySQL #1054 unknown column

I need a query that will pull only records which match the user provided search query. The tricky part is that the information that the user is searching for is in another table. My application is pulling records from a table called 'computers'. Inside 'computers' there's a column that has an ID number of a printer (default_printer). All of the printer information such as name (which the user is searching for) is inside the 'printers' table which the user is searching for.

So naturally I need to list all computer information but at the same time resolve the ID inside the 'computers' table to the name inside the 'printers' table. Here's the query that I've come up with so far:

     SELECT c.id, c.name, p.name default_printer, c.description
       FROM computers c, groups g
 INNER JOIN printers p
         ON g.default_printer = p.id OR c.default_printer = p.id
      WHERE p.name LIKE 'mfd%'

The problem is I'm getting the error: #1054 - Unknown column 'c.default_printer' in 'on clause'. The 'computers' table has a column called 'default_printer'.

Here's my table schema:

Table name: computers
Columns: id, name, description, default_printer, report_date, guid

Table name: printers
Columns: id, name, path, location, description

Table name: groups
Columns: id, name, description, default_printer

I'm stuck, help!

@N.B. I have. Trust me, I don't post here unless I'm really stuck. I suck at SQL queries but this just has me confused. — Nykad, Nov 20 '12 at 17:06
I don't see why you need help, you obviously know how to join tables, how to use aliases, how to create them etc. - what's surprising is that you have a column named `default_printer` in a table, and your SQL says `p.name default_printer` and if that didn't make you raise your eyebrows and try a few different approaches then you lack curiosity, and curiosity makes up for a good programmer. — N.B., Nov 20 '12 at 17:09
@Nykad Did getting rid of the comma join and replacing it with an explicit `JOIN` fix it as per my answer? — Ray, Nov 20 '12 at 17:09
@Ray I'm not sure you understand what I'm trying to accomplish. The user will type in a name of a printer (thus the WHERE p.name LIKE 'mfd%' line). Get the printer ID from the 'printers' table, then pull the ID's that I have a display the records in 'computers'/'groups' that match their 'default_printer' column. The 'defaut_printer' column in both 'groups' and 'computers' tables are just ID numbers to the actual printer in the 'printers' table. I hope that makes sense. So when I tried your suggestion i lost access to my alias 'p.id'. — Nykad, Nov 20 '12 at 17:24

Ray · Accepted Answer · 2012-11-20T17:10:39.713

2

Don't mix comma notation for joins and explicit join notation, you'll loose access to some of your alias names. If you don't have access to the alias, you can't refer to its columns. I'm assuming groups has a column id

Also, as noted elsewhere, I suggest you use a different name for your label 'default_printer' while you troubleshoot this and for clarity.

SELECT c.id, c.name, p.name the_default_printer, c.description
   FROM computers c
   INNER JOIN groups g  ON g.id = c.guid
   INNER JOIN printers p
     ON g.default_printer = p.id OR c.default_printer = p.id
  WHERE p.name LIKE 'mfd%'

edited Nov 20 '12 at 17:10

answered Nov 20 '12 at 16:55

Ray

40,256
21
101
138

Thanks for the suggestion. I'm pretty sure understand what you're saying. Just to be clear, I would have to matching the column 'default_printer' in the groups table with the 'id' column in the 'printers' table. Thank you for your help. – Nykad Nov 20 '12 at 17:17

score 1 · Answer 2 · answered Nov 20 '12 at 16:55

1

You are missing a comma between p.name and default_printer

answered Nov 20 '12 at 16:55

Joe Meyer

4,315
20
28

3

default_printer is an alias for p.name. The space implies the 'AS' keyword. From what I understand this is valid syntax. – Nykad Nov 20 '12 at 17:02
1

I tried the 'AS' keyword. No difference. It's complaining about c.default_printer. – Nykad Nov 20 '12 at 17:08

mySQL query - #1054 - Unknown column 3

2 Answers2