SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    left join/inner join question

    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.

    PHP Code:
    SELECT users.*, locations.locationsNameuserscv.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:

    PHP Code:
    CREATE TABLE IF NOT EXISTS `users` (
      `
    usersIdint(11NOT NULL auto_increment,
      `
    usersNamevarchar(100character set utf8 NOT NULL default '',
      `
    usersAddress1varchar(100character set utf8 default NULL,
      `
    usersAddress2varchar(100character set utf8 default NULL,
      `
    usersStateIdint(11) default '0',
      `
    usersLocationIdint(11) default '0',
      `
    usersCountryIdint(11) default '0',
      `
    usersZipvarchar(20character set utf8 default NULL,
      `
    usersUsernamevarchar(100character set utf8 NOT NULL default '',
      `
    usersPasswordvarchar(100character set utf8 NOT NULL default '',
      `
    usersEmailvarchar(100character set utf8 NOT NULL default '',
      `
    usersTownvarchar(100character set utf8 default NULL,
      `
    usersDobdate default NULL,
      `
    usersGenderenum('NA','Male','Female'character set utf8 default 'NA',
      `
    usersWorkPhonevarchar(35character set utf8 default '0',
      `
    usersMobilePhonevarchar(35character set utf8 NOT NULL default '0',
      `
    usersHomePhonevarchar(35character set utf8 NOT NULL default '0',
      `
    usersAllowCVSearchtinyint(11NOT NULL default '1',
      `
    usersCVTextlongtext character set utf8 NOT NULL,
      `
    usersLastUpdateddate NOT NULL default '0000-00-00',
      `
    usersMailingListenum('yes','no'character set utf8 NOT NULL default 'yes',
      `
    usersCvMatchingenum('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` (
      `
    cvIdint(10unsigned NOT NULL auto_increment,
      `
    cvNamevarchar(250) default NULL,
      `
    cvDatedatetime default NULL,
      `
    cvFilevarchar(150) default NULL,
      `
    usersIdint(10unsigned default NULL,
      `
    usersCVTextlongtext,
      `
    usersAllowCVSearchtinyint(11unsigned default '1',
      `
    Statusvarchar(45) default 'active',
      
    PRIMARY KEY  (`cvId`)
    ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=11462 ;

    CREATE TABLE IF NOT EXISTS `locations` (
      `
    locationsIdint(11NOT NULL auto_increment,
      `
    locationsNamevarchar(255NOT NULL default '',
      `
    locationsStatusvarchar(45) default 'active',
      
    PRIMARY KEY  (`locationsId`)
    ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=69 

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Do all users have a locationid that exists in the locations table? If yes, you can use an INNER JOIN, if no (and you want all users) use a LEFT JOIN.

    Do all users have a userid that exists in the cv table (= do all users have a cv)? If yes, you can use an INNER JOIN, if no (and you want all users) use a LEFT JOIN.

  3. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Do all users have a locationid that exists in the locations table? If yes, you can use an INNER JOIN, if no (and you want all users) use a LEFT JOIN.

    Do all users have a userid that exists in the cv table (= do all users have a cv)? If yes, you can use an INNER JOIN, if no (and you want all users) use a LEFT JOIN.
    Not all users have a locationid or a userid that exists in the cv table. Does this mean I should use LEFT JOIN on both? I tried this earlier from phpMyadmin, but it took ages for it to load and I ended up just cancelling it while it was loading.

    Any idea why it takes so long?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Yes, use LEFT JOIN on both.
    Try putting an index on usersLocationId.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •