Need the Opposite of an Inner Join

[solepixel]

I'm using Pods with Wordpress and I have a Pod called "Events". Events can be public or Member, and to determine the difference, the "Member" field has a value (of the corresponding member in another table).

I am using this query to accomplish this:

SELECT SQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modified
FROM wp_pod p
INNER JOIN wp_pod_rel r ON p.id = r.pod_id
INNER JOIN wp_pod_tbl_members m ON r.tbl_row_id = m.id AND m.id IS NOT NULL
INNER JOIN `wp_pod_tbl_event` t ON t.id = p.tbl_row_id
WHERE p.datatype = 2
ORDER BY date_time DESC

Now, what I would like to do is get the opposite event records. Where there is no associated member. I have tried the following 2 queries, but they give me all the events, and not just non-member events:

/*
 * Attempt 1
 */
SELECT SQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modified
FROM wp_pod p
LEFT JOIN wp_pod_rel r ON p.id = r.pod_id
LEFT JOIN `wp_pod_tbl_event` t ON t.id = p.tbl_row_id
LEFT JOIN wp_pod_tbl_members m ON r.tbl_row_id = m.id
WHERE p.datatype = 2 AND ( m.id IS NULL )
ORDER BY date_time DESC;
 
/*
 * Attempt 2
 */
SELECT SQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modified
FROM wp_pod p
LEFT JOIN wp_pod_rel r ON p.id = r.pod_id
LEFT JOIN `wp_pod_tbl_event` t ON t.id = p.tbl_row_id
LEFT JOIN wp_pod_tbl_members m ON r.tbl_row_id = m.id AND m.id IS NULL
WHERE p.datatype = 2
ORDER BY date_time DESC;

Any Ideas on how to accomplish this? Here are the tables I'm using:

/*
 * Table: wp_pod
 */
 
CREATE TABLE `wp_pod` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,                                        
  `tbl_row_id` int(10) unsigned DEFAULT NULL,                                           
  `datatype` smallint(5) unsigned DEFAULT NULL,                                         
  `name` varchar(128) DEFAULT NULL,                                                     
  `created` datetime DEFAULT NULL,                                                      
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
  `author_id` int(10) unsigned DEFAULT NULL,                                            
  PRIMARY KEY (`id`),                                                                   
  KEY `datatype_idx` (`datatype`)                                                       
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
/*
 * Table: wp_pod_rel
 */
CREATE TABLE `wp_pod_rel` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,        
  `pod_id` int(10) unsigned DEFAULT NULL,               
  `sister_pod_id` int(10) unsigned DEFAULT NULL,        
  `field_id` int(10) unsigned DEFAULT NULL,             
  `tbl_row_id` int(10) unsigned DEFAULT NULL,           
  `weight` smallint(5) unsigned DEFAULT '0',            
  PRIMARY KEY (`id`),                                   
  KEY `field_id_idx` (`field_id`)                       
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 
/*
 * Table: wp_pod_tbl_members
 */
CREATE TABLE `wp_pod_tbl_members` (                  
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,       
  `name` varchar(128) DEFAULT NULL,                    
  `slug` varchar(128) DEFAULT NULL,                    
  `location` varchar(128) DEFAULT NULL,                
  PRIMARY KEY (`id`)                                   
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
/*
 * Table: wp_pod_tbl_event
 */
 
CREATE TABLE `wp_pod_tbl_event` (                    
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,                    
  `slug` varchar(128) DEFAULT NULL,                    
  `date_time` datetime DEFAULT NULL,                   
  `description` longtext,                              
  `location` longtext,                                 
  PRIMARY KEY (`id`)                                   
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

[r937]

your first attempt was really close

only the 3rd join should be a LEFT OUTER JOIN

by the way, you don't need DISTINCT

[solepixel]

AWE Thanks!

BTW, I agree, it's part of the select statement that I can't change.

[solepixel]

Hmmm. I tried this query and I'm still getting ALL the events (member and non-member)

SELECT SQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modified
FROM wp_pod p
LEFT JOIN wp_pod_rel r ON p.id = r.pod_id
LEFT OUTER JOIN wp_pod_tbl_members m ON r.tbl_row_id = m.id
INNER JOIN `wp_pod_tbl_event` t ON t.id = p.tbl_row_id
WHERE p.datatype = 2 AND ( m.id IS NULL )
ORDER BY date_time DESC

[r937]

Hmmm. I tried this query ...

why?

as i mentioned, you need your first attempt, but with only the 3rd join as a LEFT OUTER JOIN --

SELECT SQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modified
FROM wp_pod p
INNER JOIN wp_pod_rel r ON p.id = r.pod_id
INNER JOIN wp_pod_tbl_event t ON t.id = p.tbl_row_id
LEFT JOIN wp_pod_tbl_members m ON r.tbl_row_id = m.id
WHERE p.datatype = 2 AND m.id IS NULL 
ORDER BY date_time DESC;

[solepixel]

Ohhh. my mistake. I thought you meant the first of the 2 attempts below my original query that I used to get member events.


Hmm. something must be wrong with my data because with this query, I still get member and non-member records:

SELECT SQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modified
FROM ecsp_pod p
INNER JOIN ecsp_pod_rel r ON p.id = r.pod_id
INNER JOIN ecsp_pod_tbl_event t ON t.id = p.tbl_row_id
LEFT OUTER JOIN ecsp_pod_tbl_members m ON r.tbl_row_id = m.id
WHERE p.datatype = 2 AND m.id IS NULL 
ORDER BY date_time DESC;

[SpacePhoenix]

solepixel, can you post a few sample entries for each of the 4 tables?