Results 1 to 5 of 5
Thread: IFNULL subselect?
Jan 29, 2006, 14:12 #1
- Join Date
- Aug 2004
- 0 Post(s)
- 0 Thread(s)
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.
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