Description:
Left join returns abnormal results when executing query given below
SELECT a. * , p . *
FROM `eskl_photo_album` p
LEFT JOIN `eskl_photos` a ON p.`palb_id` = a.`palb_id`
WHERE p.user_id = '2'
GROUP BY a.`palb_id
result:
photo_id palb_id photo_file photo_caption photo_posted palb_id user_id palb_name
NULL NULL NULL NULL NULL 3 2 album3
1 1 dfhfdhdfh 0000-00-00 00:00:00 1 2 album1
2 2 dsgsfy 0000-00-00 00:00:00 2 2 album2
How to repeat:
--TABLE I
-- version 2.11.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 23, 2009 at 08:48 AM
-- Server version: 5.0.51
-- PHP Version: 5.2.5
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `eShackle`
--
-- --------------------------------------------------------
--
-- Table structure for table `eskl_photos`
--
CREATE TABLE IF NOT EXISTS `eskl_photos` (
`photo_id` int(11) NOT NULL auto_increment,
`palb_id` int(11) NOT NULL,
`photo_file` varchar(255) NOT NULL,
`photo_caption` varchar(255) NOT NULL,
`photo_posted` datetime NOT NULL,
PRIMARY KEY (`photo_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `eskl_photos`
--
INSERT INTO `eskl_photos` (`photo_id`, `palb_id`, `photo_file`, `photo_caption`, `photo_posted`) VALUES
(1, 1, 'dfhfdhdfh', '', '0000-00-00 00:00:00'),
(2, 2, 'dsgsfy', '', '0000-00-00 00:00:00');
--TABLE II
-- phpMyAdmin SQL Dump
-- version 2.11.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 23, 2009 at 08:49 AM
-- Server version: 5.0.51
-- PHP Version: 5.2.5
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `eShackle`
--
-- --------------------------------------------------------
--
-- Table structure for table `eskl_photo_album`
--
CREATE TABLE IF NOT EXISTS `eskl_photo_album` (
`palb_id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL,
`palb_name` varchar(255) NOT NULL,
PRIMARY KEY (`palb_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `eskl_photo_album`
--
INSERT INTO `eskl_photo_album` (`palb_id`, `user_id`, `palb_name`) VALUES
(1, 2, 'album1'),
(2, 2, 'album2'),
(3, 2, 'album3'),
(4, 2, 'album4'),
(5, 2, 'album5');
apply this query
SELECT a. * , p . *
FROM `eskl_photo_album` p
LEFT JOIN `eskl_photos` a ON p.`palb_id` = a.`palb_id`
WHERE p.user_id = '2'
GROUP BY a.`palb_id
Suggested fix:
group by column from second table in left join must be restricted
