SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need the Opposite of an Inner Join

    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:
    Code MySQL:
    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:
    Code MySQL:
    /*
     * 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:
    Code MySQL:
    /*
     * 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;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your first attempt was really close

    only the 3rd join should be a LEFT OUTER JOIN

    by the way, you don't need DISTINCT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AWE Thanks!

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

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm. I tried this query and I'm still getting ALL the events (member and non-member)

    Code MySQL:
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by solepixel View Post
    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 --
    Code:
    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;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code MySQL:
    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;

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,998
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    solepixel, can you post a few sample entries for each of the 4 tables?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


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
  •