Geek Answers Handbook

Ambiguous "id" field

I have the following two tables:

system - id - systemName - idOrganization organization - id - officeSymbol

I run the following query and get id ambiguous error:

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

As you can see, I do not select the id column. If I put system.id in the list of fields for selection, I no longer get this error. Unfortunately, the method of processing this data I can not return id - we do not want it to be displayed to the user.

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

Note. LEFT JOIN is intentional because not all systems will be assigned to the 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;
+4
source share
7 answers

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 is working fine.

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

In your comment on the @Artem Barger you said:

I select only 5 of 42 fields shared between these two tables

Are there any other fields in tables and / or queries?

Since you have a syntax error, each comma can make a difference.

+5
source

I'm not sure what is going on since I'm not familiar enough with mySql, but I always prefer to name columns like SystemID and OrganizationID rather than "ID".

Naming your columns this way will 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
+1
source

Not sure why your request will generate: usually you get this error message:

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

Are you sure the query is exactly giving you this error? If you trimmed it for SO, you could have cut the error.

+1
source

I really don't know mysql, but only sqlserver, however, using field names like "id" and table names like "system", I'm sure the square bracket contains all the table and column names. Maybe take a picture?

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

Michael, putting the identifier in single quotation marks (`) (or square brackets, like in SQL Server, or something that works for mysql) will fix the problem?

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

You tried:

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

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

-1
source

It seems very strange to me that you will get this error (although my main experience is not related to mysql). I would try with the overlay of your tables.

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

More articles:


All Articles