Why isn't this SQL code working?

Question

I created 3 different tables and the coding for it is

CREATE TABLE `shirt` (
   `id` int(11) not null,
   `name` varchar(32),
   PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `shirt` (`id`, `name`) VALUES 
('1', 'vneck'),
('2', 'scoop neck');

CREATE TABLE `shirt_size` (
   `shirtId` int(11) not null,
   `sizeId` int(11) not null,
   PRIMARY KEY (`shirtId`,`sizeId`),
   KEY `sizeId` (`sizeId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `shirt_size` (`shirtId`, `sizeId`) VALUES 
('1', '2'),
('1', '3'),
('1', '4'),
('1', '5'),
('2', '1'),
('2', '2'),
('2', '3'),
('2', '4'),
('2', '5'),
('2', '6'),
('2', '7');

CREATE TABLE `size` (
   `id` int(11) not null,
   `name` varchar(4),
   PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `size` (`id`, `name`) VALUES 
('1', 'xs'),
('2', 's'),
('3', 'm'),
('4', 'l'),
('5', '1x'),
('6', '2x'),
('7', '3x');

and I'm querying it with this

SELECT shirt.name, size.name 
FROM   shirt
       INNER JOIN
          shirt_size ON shirt_size.shirtId = shirt.id
        INNER JOIN
          size ON size.id = shirt_size.sizeId

but the table that results only shows the name of the shirt, I need the sizes column to show up on the screen too. In the FROM part I put shirt, size but was getting an error. Upon looking farther into it I seen lots of people only putting the first table name in the FROM part. I don't see how it's suppose to represent the size.name column. What am I doing wrong?

Answer 1

They have the same column name (although from different tables). You need to supply an ALIAS on one of the columns (or both), eg

SELECT shirt.name as ShirtName, 
       size.name as SizeName
FROM   shirt
       INNER JOIN
            shirt_size ON shirt_size.shirtId = shirt.id
       INNER JOIN
             size ON size.id = shirt_size.sizeId

• SQLFiddle Demo Link