MySQL join empty rows

Question

I'm trying to construct a simple query in for getting users with particular meta fields (this is in Wordpress, but doesn't matter cause it's raw sql)

A simple query i did looks like this

SELECT * FROM wp_sb_users u
 LEFT OUTER JOIN wp_sb_usermeta m ON (u.ID=m.user_id)
 LEFT OUTER JOIN wp_sb_usermeta mm ON (u.ID=mm.user_id)
 LEFT OUTER JOIN wp_sb_usermeta mmm ON (u.ID=mmm.user_id)
WHERE
 m.meta_key = "autostatus" AND
 mm.meta_key = "first_name" AND
 mmm.meta_key = "last_name"

though i have only one small trouble -- if mmm.meta_key = "last_name" doesn't exist at all, the row isn't returned.. i tried mmm.meta_key <=> "last_name" but then it takes any other meta_key (like "user_email") and puts there in row, which then results in having a user with first name Alex and last name [email protected]

I also tried (mmm.meta_key = "last_name" OR mmm.meta_key IS NULL), but it doesn't work too

Help me figure it out please

PS the tables structure:

CREATE TABLE IF NOT EXISTS `wp_sb_users` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `user_login` varchar(60) NOT NULL default '',
  `user_pass` varchar(64) NOT NULL default '',
  `user_nicename` varchar(50) NOT NULL default '',
  `user_email` varchar(100) NOT NULL default '',
  `user_url` varchar(100) NOT NULL default '',
  `user_registered` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL default '',
  `user_status` int(11) NOT NULL default '0',
  `display_name` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=30 ;

CREATE TABLE IF NOT EXISTS `wp_sb_usermeta` (
  `umeta_id` bigint(20) unsigned NOT NULL auto_increment,
  `user_id` bigint(20) unsigned NOT NULL default '0',
  `meta_key` varchar(255) default NULL,
  `meta_value` longtext,
  PRIMARY KEY  (`umeta_id`),
  KEY `user_id` (`user_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=792 ;

please explain what you are trying to accomplish here, and maybe include the structure of the above tables? this will help us help you better. — BigFatBaby
– BigFatBaby, Commented Jun 1, 2011 at 8:29
Please tell us what you get from this query and what would you expect to receive — Tudor Constantin
– Tudor Constantin, Commented Jun 1, 2011 at 8:29
sorry, added table structure.. so what i am trying to do is get users with their particular meta fields defined in other table, EVEN if no such meta field exists — Alex K
– Alex K, Commented Jun 1, 2011 at 8:40

Steve Mayne · Accepted Answer · 2011-06-01 08:31:45Z

4

SELECT * FROM wp_sb_users u
 LEFT OUTER JOIN wp_sb_usermeta m ON (u.ID=m.user_id and m.meta_key = "autostatus")
 LEFT OUTER JOIN wp_sb_usermeta mm ON (u.ID=mm.user_id and mm.meta_key = "first_name")
 LEFT OUTER JOIN wp_sb_usermeta mmm ON (u.ID=mmm.user_id and mmm.meta_key = "last_name")

answered Jun 1, 2011 at 8:31

Steve Mayne

23k4 gold badges53 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex K Over a year ago

you know what, i just tried that and it worked! seems like WHERE is kinda strict for selects, and putting conditions in ON is more loosy

Steve Mayne Over a year ago

Your meta_key is part of your join criteria. If you do your original query without your WHERE clause, you'll realise what the dataset you were filtering looked like.

Stijn de Witt Over a year ago

And just a tip for when you do queries with no WHERE condition, end your query with something like LIMIT 100 so you don't try to fetch a million records, which can easily happen when outer joining over multiple tables

Collectives™ on Stack Overflow

MySQL join empty rows

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related