Hi guys!
I am trying to select all users from a database, whilst also selecting 2 additional fields from 2 seperate tables.
The query I am using is this…However, it only returns 4,000 rows, when there are over 20,000 rows in the users table.
SELECT users.*, locations.locationsName, userscv.cvFile
FROM users
LEFT JOIN locations ON locations.locationsId = users.usersLocationId
INNER JOIN userscv ON userscv.usersId = users.usersId
GROUP BY users.usersId
Here are the tables:
CREATE TABLE IF NOT EXISTS `users` (
`usersId` int(11) NOT NULL auto_increment,
`usersName` varchar(100) character set utf8 NOT NULL default '',
`usersAddress1` varchar(100) character set utf8 default NULL,
`usersAddress2` varchar(100) character set utf8 default NULL,
`usersStateId` int(11) default '0',
`usersLocationId` int(11) default '0',
`usersCountryId` int(11) default '0',
`usersZip` varchar(20) character set utf8 default NULL,
`usersUsername` varchar(100) character set utf8 NOT NULL default '',
`usersPassword` varchar(100) character set utf8 NOT NULL default '',
`usersEmail` varchar(100) character set utf8 NOT NULL default '',
`usersTown` varchar(100) character set utf8 default NULL,
`usersDob` date default NULL,
`usersGender` enum('NA','Male','Female') character set utf8 default 'NA',
`usersWorkPhone` varchar(35) character set utf8 default '0',
`usersMobilePhone` varchar(35) character set utf8 NOT NULL default '0',
`usersHomePhone` varchar(35) character set utf8 NOT NULL default '0',
`usersAllowCVSearch` tinyint(11) NOT NULL default '1',
`usersCVText` longtext character set utf8 NOT NULL,
`usersLastUpdated` date NOT NULL default '0000-00-00',
`usersMailingList` enum('yes','no') character set utf8 NOT NULL default 'yes',
`usersCvMatching` enum('yes','no') character set utf8 NOT NULL default 'yes',
PRIMARY KEY (`usersId`),
KEY `usersCvMatching` (`usersCvMatching`),
FULLTEXT KEY `usersCVText` (`usersCVText`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=34893 ;
CREATE TABLE IF NOT EXISTS `userscv` (
`cvId` int(10) unsigned NOT NULL auto_increment,
`cvName` varchar(250) default NULL,
`cvDate` datetime default NULL,
`cvFile` varchar(150) default NULL,
`usersId` int(10) unsigned default NULL,
`usersCVText` longtext,
`usersAllowCVSearch` tinyint(11) unsigned default '1',
`Status` varchar(45) default 'active',
PRIMARY KEY (`cvId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=11462 ;
CREATE TABLE IF NOT EXISTS `locations` (
`locationsId` int(11) NOT NULL auto_increment,
`locationsName` varchar(255) NOT NULL default '',
`locationsStatus` varchar(45) default 'active',
PRIMARY KEY (`locationsId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=69 ;