SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Aug 2004
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    IFNULL subselect?

    I've tried very simple IFNULL subselects and they are not working. I'm using mysql 5.0.15. Is this possible?

    What I have:
    I have 2 tables: usersextra and usersextraemail
    usersextra has an email slot which i assume is the most accessed email.
    while usersextraemail contains other emails that the user may have.

    Problem:
    Whenever I try to get users name held in usersextra by email I would like to get the name whether the email exist in usersextraemail or usersextra


    My current fix:
    i've put all the emails in usersextraemail with a PriorityContactLevel column.
    however i would like to know if i could have done it with my original table structure.


    Is there any way to rewrite this logic in another form (nested outer joins?)

    Code:
    SELECT DISTINCT(c.ID) AS ID, c.UserCreator, c.ACommentText, c.ACommentCreditName, c.ACommentTitle, c.ACommentCreditHTTP, c.DateSubmitted, 
    IFNULL(e.firstname, SELECT ue.firstname FROM UsersExtraEmail AS uee LEFT JOIN  UsersExtra AS ue ON ue.Users_UsersID=uee.Users_UsersID WHERE uee.email=c.ACommentCreditEmail), 
    e.lastname, 
    e.http  
    FROM ArticleComment AS c  
    LEFT JOIN UsersExtra AS e On e.email=c.ACommentCreditEmail  
    WHERE c.Article_ID=14

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    kinda hard to tell what you're doing without seeing table layouts or sample values

    if e.firstname is null, your query appears to get firstname out of a different table -- but wouldn't e.lastname be null then too? wouldn't you get lastname from the other table in that case too? and same with http?

    the following assumes that UsersExtra and UsersExtraEmail have the same layout
    Code:
    SELECT c.ID
         , c.UserCreator
         , c.ACommentText
         , c.ACommentCreditName
         , c.ACommentTitle
         , c.ACommentCreditHTTP
         , c.DateSubmitted
         , coalesce(e.firstname,uee.firstname) as firstname
         , coalesce(e.lastname,uee.lastname) as lastname
         , coalesce(e.http,uee.http) as http
      FROM ArticleComment AS c  
    LEFT outer
      JOIN UsersExtra AS e 
        On e.email=c.ACommentCreditEmail  
    LEFT outer
      JOIN UsersExtraEmail AS uee 
        On uee.email=c.ACommentCreditEmail   
     WHERE c.Article_ID=14
    by the way, please note, DISTINCT is not a function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2004
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table layout and sample data

    Not sure if you already provided the answer... but here is what you requested.

    Table layout: http://www.sitepoint.com/forums/atta...chmentid=25262

    Sample data
    Code:
    CREATE TABLE ArticleComment (
      ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
      AComment TEXT NULL,
      ACommentCreditEmail VARCHAR(32) NULL,
      UserCreator INTEGER UNSIGNED NULL,
      PRIMARY KEY(ID)
    );
    
    CREATE TABLE Users (
      ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
      UserLoginName VARCHAR(32) NULL,
      UserPassword VARCHAR(32) NULL,
      PRIMARY KEY(ID)
    );
    
    INSERT INTO Users (ID, UserLoginName, UserPassword) VALUES(1, 'leblanc' , '');
    INSERT INTO Users (ID, UserLoginName, UserPassword) VALUES(2, 'auser', 'auser'); 
    INSERT INTO Users (ID, UserLoginName, UserPassword) VALUES(3, 'buser', 'buser'); 
    
    
    CREATE TABLE UsersExtra (
      ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
      Users_ID INTEGER UNSIGNED NOT NULL,
      firstname VARCHAR(50) NULL,
      lastname VARCHAR(50) NULL,
      lastlogin DATETIME NULL,
      registereddate DATETIME NULL,
      email VARCHAR(32) NULL,
      PRIMARY KEY(ID),
      INDEX UsersExtra_FKIndex1(Users_ID)
    );
    
    INSERT INTO UsersExtra (ID, Users_ID, firstname, lastname, lastlogin, registereddate, email) VALUES(1, 1, 'leblanc', 'meneses', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'leblanc1@tamu.edu');
    INSERT INTO UsersExtra (ID, Users_ID, firstname, lastname, lastlogin, registereddate, email) VALUES(2, 2, 'auser', 'auser', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'email');
    INSERT INTO UsersExtra (ID, Users_ID, firstname, lastname, lastlogin, registereddate, email) VALUES(3, 3, 'buser', 'buser', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'email');
    
    
    CREATE TABLE UsersExtraEmail (
      ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
      UsersExtra_ID INTEGER UNSIGNED NOT NULL,
      email VARCHAR(32) NULL,
      PRIMARY KEY(ID),
      INDEX UsersExtraEmail_FKIndex1(UsersExtra_ID)
    );
    
    INSERT INTO UsersExtraEmail (ID, UsersExtra_ID, email) VALUES(1, 1, 'leblanc2@tamu.edu');
    INSERT INTO UsersExtraEmail (ID, UsersExtra_ID, email) VALUES(2, 1, 'leblanc3@tamu.edu');

    Goal if articlecomment has UserCreator populated with an integer >=1 the find then inner join with users.tbl and inner join with usersextra to get lastname and firstname

    else if integer is 0 then see if AcommentCreditEmail exist in UsersExtra or UsersExtraEmail and bring the corresponding firstname and lastname.


    I used left joins to allow to test both conditions.. if doesn't exit returns nulls if exist returns right values.

    I used distinct to return only 1 row. (mysql function)
    Attached Images Attached Images

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Goal if articlecomment has UserCreator populated with an integer >=1 the find then inner join with users.tbl and inner join with usersextra to get lastname and firstname ... else if integer is 0 then see if AcommentCreditEmail exist in UsersExtra or UsersExtraEmail and bring the corresponding firstname and lastname.
    Code:
    SELECT ac.AComment
         , ac.ACommentCreditEmail
         , ac.UserCreator
         , u.ID
         , u.UserLoginName 
         , u.UserPassword 
         , ue.firstname
         , ue.lastname
      FROM ArticleComment AS ac  
    INNER
      JOIN Users as u
        ON u.ID = ac.UserCreator
    INNER
      JOIN UsersExtra AS ue 
        ON ue.Users_ID = u.ID
     WHERE ac.Article_ID=14
       AND ac.UserCreator >= 1
    UNION ALL 
    SELECT ac.AComment
         , ac.ACommentCreditEmail
         , ac.UserCreator
         , u.ID
         , u.UserLoginName 
         , u.UserPassword 
         , ue.firstname
         , ue.lastname
      FROM ArticleComment AS ac  
    INNER 
      JOIN UsersExtra AS ue 
        ON ue.email = ac.ACommentCreditEmail
    INNER
      JOIN Users as u
        ON u.ID = ue.Users_ID  
     WHERE ac.Article_ID=14
       AND ac.UserCreator = 0 
    UNION ALL 
    SELECT ac.AComment
         , ac.ACommentCreditEmail
         , ac.UserCreator
         , u.ID
         , u.UserLoginName 
         , u.UserPassword 
         , ue.firstname
         , ue.lastname
      FROM ArticleComment AS c  
    INNER 
      JOIN UsersExtraEmail AS uee 
        ON uee.email = ac.ACommentCreditEmail
    INNER 
      JOIN UsersExtra AS ue 
        ON ue.Users_ID = uee.UsersExtra_ID
    INNER
      JOIN Users as u
        ON u.ID = ue.Users_ID  
     WHERE ac.Article_ID=14
       AND ac.UserCreator = 0 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Aug 2004
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ha - much more elegant way to solve it. Thanks.

    Thank u sir.


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
  •