IFNULL subselect?

[leblanc]

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?)

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

[r937]

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

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

[leblanc]

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

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)

[r937]

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.

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 

[leblanc]

Thank u sir.