Ambiguous `id` Field

Question

I have the following two tables:

system
- id
- systemName
- idOrganization

organization
- id
- officeSymbol

I am running the following query and receiving an id is ambiguous error:

SELECT system.systemName, organization.officeSymbol
FROM system
LEFT JOIN (organization)
ON (system.idOrganization = organization.id)

As you can see, I'm not selecting the id column. If I place system.id within the list of fields to select, I no longer receive this error. Unfortunately, the manner in which this data is handled I can't return the id - we don't want it displayed to the user.

Also, if I add GROUP BY system.systemName I no longer get the error - but this just doesn't seem like the optimal solution.

Note: The LEFT JOIN is intentional as not all systems will be assigned to an Organization.

SELECT VERSION()
--> 5.0.77-community-log

CREATE TABLE system (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `systemName` VARCHAR(45) DEFAULT NULL,
  `idOrganization` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_system_organization` (`idOrganization`),
  CONSTRAINT `fk_system_organization` 
    FOREIGN KEY (`idOrganization`) 
    REFERENCES `organization` (`id`) 
    ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE organization (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `officeSymbol` VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

Im not sure what is happening, since I'm not familiar enough with mySql, but I always prefer to name columns like SystemID and OrganizationID and not gernically like "ID".

Naming your columns this way, would result in PK-FK combinations that have the same name:

bad PK-FK names       good PK-FK names
system                system
  id                    SystemID
  systemName            systemName
  idOrganization        OrganizationID  <--

organization          organization
  id                    OrganizationID  <--
  officeSymbol          officeSymbol

Answer 2

Try DISTINCT.

SELECT DISTINCT s.systemName as name, o.officeSymbol as symbol
FROM system as s
LEFT JOIN (organization as o)
ON (s.idOrganization = o.id)

Answer 3

Have you tried:

SELECT system.systemName, organization.officeSymbol
FROM system, organization
WHERE system.idOrganization = organization.id

This should join the tables together properly, even if not all systems are assigned to an organization.

Answer 4

I don't really know mysql, only sqlserver, however, when using field names like "id" and table names like "system" i'd be sure to square bracket all of the table and columns names like so. Might be worth a shot?

SELECT
    [system].[systemName],
    [organization].[officeSymbol]
FROM
    [system]
LEFT OUTER JOIN
    [organization]
ON
    [system].[idOrganization] = [organization].[id]

Answer 5

It seems very bizarre to me that you'd be getting that error (though my main experience isn't with mysql). I'd try with aliasing your tables.

SELECT s.systemName, o.officeSymbol
FROM system as s
LEFT JOIN (organization as o)
ON (s.idOrganization = o.id)

Answer 6

Not sure why your query would be generating that: normally you get that error message when you do:

SELECT id /* This should have been qualified with system. or organization. */
FROM system
LEFT JOIN (organization)
ON (system.idOrganization = organization.id)

Are you sure that query is exactly giving you that error? If you trimmed it down for SO, you might have cut out the bug.

Answer 7

Tested on 5.0.77:

SELECT  VERSION();

VERSION()
5.0.77


CREATE TABLE organization (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `officeSymbol` VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE system (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `systemName` VARCHAR(45) DEFAULT NULL,
  `idOrganization` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_system_organization` (`idOrganization`),
  CONSTRAINT `fk_system_organization`
    FOREIGN KEY (`idOrganization`)
    REFERENCES `organization` (`id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT
INTO   organization
VALUES
        (1, 'Organization 1'),
        (2, 'Organization 2');
INSERT
INTO     system
VALUES  (1, 'System 1', 1),
        (2, 'System 2', 2);
SELECT  system.systemName, organization.officeSymbol
FROM    system
LEFT JOIN
        (organization)
ON      (system.idOrganization = organization.id);

systemName      officeSymbol
System 1        Organization 1
System 2        Organization 2

Everything works fine.

Note that LEFT JOIN is useless here, since you have a FOREIGN KEY to organization, and there will always be an organization for every given system.

In your comment to @Artem Barger's post you said:

I'm only selecting on 5 of the 42 fields shared between these 2 tables

Is it that there are other fields in the tables and/or query?

Since you have a syntactic error, every comma may matter.

Answer 8

Michael, would placing the id in single quotes (`) (or square brackets like in SQL Server, or whatever works for mysql) help fixing the issue?

...
ON (system.`idOrganization` = organization.`id`)