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;