SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: IFNULL subselect?
-
Jan 29, 2006, 15:12 #1
- 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
-
Jan 29, 2006, 15:24 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Jan 30, 2006, 11:59 #3
- 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)
-
Jan 30, 2006, 12:40 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Jan 30, 2006, 23:14 #5
- 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