SitePoint Sponsor

User Tag List

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

    Join only if there are records?

    Maybe I'm overthinking this, but I'm writing a query to search a user based on some fields in the user table, a field in another table and 2 fields in another, etc.

    My query looks like this:

    Code MySQL:
    SELECT `u`.`id`, `u`.`fname`, `u`.`lname`, `u`.`username`, `p`.`bio`, `p`.`city`, `st`.`value` AS state_value, `p`.`zip`
    FROM (`users` AS u)
    LEFT JOIN `profile` AS p ON `u`.`id` = `p`.`user`
    LEFT JOIN `schools` AS sc ON `p`.`school` = `sc`.`id` AND p.school IS NOT NULL
    LEFT JOIN `eav` AS st ON `p`.`state` = `st`.`id`
    LEFT JOIN `user_sports` AS us ON `u`.`id` = `us`.`user`
    INNER JOIN `sports` AS sp ON `us`.`sport` = `sp`.`id`
    WHERE `u`.`deleted` IS NULL AND `u`.`confirmed` IS NOT NULL AND
    (u.fname LIKE '%search%' OR u.lname LIKE '%search%' OR u.username LIKE '%search%' OR u.email LIKE '%search%' OR p.city LIKE '%search%' OR st.value LIKE '%search%' OR st.alt_value LIKE '%search%' OR p.zip LIKE '%search%' OR sp.sport LIKE '%search%' OR sc.school LIKE '%search%')
    GROUP BY `u`.`id`

    Seems to be working OK except for one little part. The LEFT JOIN `user_sports` is filtering results when a user does not have any sports setup yet. So how do I say LEFT JOIN only when there are rows so I can search those rows for a specific sport?

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use an INNER JOIN instead

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think an inner join is giving me the same results.

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Can you provided a Create Table and some sample data?

    btw: when posting SQL, can you post them formatted like below (it makes it easier to spot minor errors and you can see what's going on easier, especially if a query has sub-queries:

    Code SQL:
    SELECT
          u.id
        , u.fname
        , u.lname
        , u.username
        , p.bio
        , p.city
        , st.VALUE AS state_value
        , p.zip
    FROM
        users AS u
    LEFT JOIN
        profile AS p
    ON u.id = p.USER
    LEFT JOIN
        schools AS sc
    ON
        p.school = sc.id
    AND
        p.school IS NOT NULL
    LEFT JOIN
        eav AS st
    ON
        p.state = st.id
    LEFT JOIN
        user_sports AS us
    ON
        u.id = us.USER
    INNER JOIN
        sports AS sp
    ON
        us.sport = sp.id
    WHERE
        `u`.`deleted` IS NULL
    AND
        `u`.`confirmed` IS NOT NULL
    AND
        (    u.fname LIKE '%search%' 
            OR u.lname LIKE '%search%'
            OR u.username LIKE '%search%'
            OR u.email LIKE '%search%'
            OR p.city LIKE '%search%'
            OR st.VALUE LIKE '%search%'
            OR st.alt_value LIKE '%search%'
            OR p.zip LIKE '%search%'
            OR sp.sport LIKE '%search%'
            OR sc.school LIKE '%search%' )
    GROUP BY
        `u`.`id`
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    users
    Code MySQL:
    CREATE TABLE `users` (                                   
              `id` bigint(25) NOT NULL auto_increment,               
              `role` bigint(25) default '1',                         
              `fname` varchar(100) default NULL,                     
              `lname` varchar(100) default NULL,                     
              `username` varchar(100) default NULL,                  
              `email` varchar(150) default NULL,                     
              `password` varchar(150) default NULL,                  
              `hash` varchar(150) default NULL,                      
              `reset` tinyint(1) default '0',                        
              `lastlogin` datetime default NULL,                     
              `logins` bigint(24) default NULL,                      
              `added` timestamp NOT NULL default CURRENT_TIMESTAMP,  
              `confirmed` datetime default NULL,                     
              `deleted` datetime default NULL,                       
              PRIMARY KEY  (`id`)                                    
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    eav
    Code MySQL:
    CREATE TABLE `eav` (                                     
              `id` bigint(25) NOT NULL auto_increment,               
              `entity` varchar(100) default NULL,                    
              `attribute` varchar(100) default NULL,                 
              `value` varchar(255) default NULL,                     
              `alt_value` varchar(255) default NULL,                 
              `sort` bigint(25) default NULL,                        
              `active` tinyint(1) default '1',                       
              `added` timestamp NOT NULL default CURRENT_TIMESTAMP,  
              `deleted` datetime default NULL,                       
              PRIMARY KEY  (`id`)                                    
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    sports
    Code MySQL:

    user_sports
    Code MySQL:
    CREATE TABLE `user_sports` (                             
                   `id` bigint(25) NOT NULL auto_increment,               
                   `user` bigint(25) default NULL,                        
                   `sport` bigint(25) default NULL,                       
                   `team` bigint(25) default NULL,                        
                   `number` tinyint(3) default NULL,                      
                   `added` timestamp NOT NULL default CURRENT_TIMESTAMP,  
                   `deleted` datetime default NULL,                       
                   PRIMARY KEY  (`id`)                                    
                 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    profile
    Code MySQL:
    CREATE TABLE `profile` (                                                            
               `id` bigint(25) NOT NULL auto_increment,                                          
               `user` bigint(25) default NULL,                                                   
               `dob` date default NULL,                                                          
               `school` bigint(25) default NULL,                                                 
               `title` varchar(100) default NULL,                                                
               `headcoach` tinyint(1) default NULL,                                              
               `graduation` int(4) default NULL,                                                 
               `bio` longtext,                                                                   
               `address1` varchar(100) default NULL,                                             
               `address2` varchar(100) default NULL,                                             
               `city` varchar(60) default NULL,                                                  
               `state` bigint(25) default NULL,                                                  
               `zip` varchar(10) default NULL,                                                   
               `phone` int(10) default NULL,                                                     
               `mobile` int(10) default NULL,                                                    
               `carrier` bigint(25) default NULL,                                                
               `bestcalltime` varchar(150) default NULL,                                         
               `modified` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  
               `approved` datetime default NULL,                                                 
               PRIMARY KEY  (`id`)                                                               
             ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    sample data
    Code MySQL:
    /*Data for the table `eav` */
     
    insert  into `eav`(`id`,`entity`,`attribute`,`value`,`alt_value`,`sort`,`active`,`added`,`deleted`) values (1,'state',NULL,'AK','Alaska',NULL,1,'2009-12-02 16:04:20',NULL),(2,'state',NULL,'AL','Alabama',NULL,1,'2009-12-02 16:04:30',NULL),(53,'school_type',NULL,'High School','HS',NULL,1,'2009-12-02 16:22:34',NULL),(65,'school_type',NULL,'College',NULL,NULL,1,'2009-12-17 16:30:44',NULL);
     
    /*Data for the table `profile` */
     
    insert  into `profile`(`id`,`user`,`dob`,`school`,`title`,`headcoach`,`graduation`,`bio`,`address1`,`address2`,`city`,`state`,`zip`,`phone`,`mobile`,`carrier`,`bestcalltime`,`modified`,`approved`) values (1,1,'1983-01-26',1,NULL,NULL,2011,'Lorem ipsum.','111 Countryville Road',NULL,'Birmingham',2,'35215',NULL,2055558421,1,NULL,'2009-12-16 10:31:02',NULL),(2,NULL,NULL,NULL,NULL,NULL,2010,NULL,'6450 Husky Parkway',NULL,'Trussville',2,'35173',2055554000,NULL,NULL,NULL,'2009-12-16 10:30:52',NULL),(3,2,'1981-09-24',1,NULL,NULL,2010,NULL,'123 Maple Street',NULL,'Alabaster',2,'35555',NULL,2055554446,57,NULL,'2009-12-10 16:32:58',NULL);
     
    /*Data for the table `sports` */
     
    insert  into `sports`(`id`,`hash`,`sport`,`sort`,`added`,`deleted`) values (1,'general','General',1,'2009-12-04 14:09:35',NULL),(2,'football','Football',2,'2009-12-04 14:09:36',NULL),(3,'baseball','Baseball',3,'2009-12-04 14:09:37',NULL),(4,'soccer','Men\'s Soccer',5,'2009-12-04 16:11:52',NULL),(5,'basketball','Men\'s Basketball',4,'2009-12-09 15:48:25',NULL),(6,'lacrosse','Men\'s Lacrosse',6,'2009-12-09 15:48:46',NULL),(7,'volleyball','Men\'s Volleyball',7,'2009-12-09 15:49:02',NULL),(8,'softball','Softball',8,'2009-12-09 15:53:50',NULL),(9,'volleyball-womens','Women\'s Volleyball',9,'2009-12-09 15:53:50',NULL),(10,'basketball-womens','Woment\'s Basketball',10,'2009-12-09 15:54:06',NULL),(11,'soccer-womens','Women\'s Soccer',11,'2009-12-09 15:54:19',NULL),(12,'lacrosse-womens','Women\'s Lacrosse',12,'2009-12-09 15:54:31',NULL),(13,'track-and-field','Track & Field',13,'2009-12-09 15:54:49',NULL),(14,'ice-hockey','Ice Hockey',14,'2009-12-09 15:55:00',NULL),(15,'gymnastics','Gymnastics',15,'2009-12-09 15:55:13',NULL),(16,'golf','Golf',16,'2009-12-09 15:55:15',NULL),(17,'tennis','Tennis',17,'2009-12-09 15:55:24',NULL),(18,'other','Other',18,'2009-12-09 15:55:33',NULL);
     
    /*Data for the table `user_sports` */
     
    insert  into `user_sports`(`id`,`user`,`sport`,`team`,`number`,`added`,`deleted`) values (5,1,1,NULL,34,'2009-12-04 14:48:43',NULL),(6,1,2,NULL,NULL,'2009-12-07 08:03:29',NULL),(7,1,4,NULL,NULL,'2009-12-07 08:03:40',NULL),(8,1,3,NULL,NULL,'2009-12-07 14:28:27',NULL);
     
    /*Data for the table `users` */
     
    insert  into `users`(`id`,`role`,`fname`,`lname`,`username`,`email`,`password`,`hash`,`reset`,`lastlogin`,`logins`,`added`,`confirmed`,`deleted`) values (1,1,'Carl','Smith','carl','carl@email.com','1a1dc91c907325c69271ddf0c944bc72','6f1ed002ab5595859014ebf0951522d9',0,'2009-12-18 10:42:40',1,'2009-12-02 13:55:34','2009-12-17 10:42:42',NULL),(2,1,'Brian','Jones','brian','brian@email.com','1a1dc91c907325c69271ddf0c944bc72','fb0dd8d460c6c5a6dab9cffa6e0228ec',0,NULL,NULL,'2009-12-04 13:30:45','2009-12-18 10:51:18',NULL);

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Based on the sample data the user 'Carl' has been correctly selected as he has a sport 'General' setup
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is what I'm trying to explain. Carl comes up, but Brian does not, since Brian does not have a sport. So what I'm trying to do is search the table of uses based on various criteria, so if a user searches for 'AL', it should pull up Carl and Brian, but since Brian does not have a user_sport record, he's filtered out. I'm trying to figure out how to include him in the results.

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You should dynamically build the part of the query that contains the search criteria : if the user searches for all, don't add that part of the search. That way the left join will work as a left join, and extract also those users that don't have any data in the left joined tables.
    As soon as you add a WHERE condition on a left joined table (other than IS NULL or IS NOT NULL), the left join becomes an inner join as far as the result is concerned.


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
  •