How to get aournd undefined column error in MySQL without added another column

What I am trying to do is pretty simple (or so I thought).  I may get an account name to look up either of the form "a" or "a@b". In the former case I want to compare against one column, in the later two columns concatenated.

Below shows what I have tried and the results.  I realize that the easy answer is to just add another column in the table for email but that seems like a duplication of information.  Another possibility is that it may work keeping all names in one column and then splitting them out into two parts if there is an "@" symbol but I like having two columns as it makes for easy sorting by either column when looking at the table directly.

I'm not sure yet whether the system I am setting will be able to insert into multiple tables so I currently trying to keep this as a one table solution.

However, I also realize that the answer may be "you can't do what you are trying to do", which is fine but I thought I would ask just in case there is a way.


#
# In both cases below consider all instances of "a@b" to also sometimes be of the form "a"
#
 
#
# Case 1: Does not return row in case of "a@b", does work in case of "a"
#
SELECT 
	`pwd` 
FROM `accts` 
WHERE  
	IF (
		INSTR('@', 'a@b') != 0, 
		`user` = 'a@b' AND `realm` IS NULL, 
		CONCAT_WS('@', `user`, `realm`) = 'a@b'
	)
 
#
# Case 2: Gives undefined column error
#
SELECT 
	`pwd`, 
	CONCAT_WS('@', `user`, `realm`) AS email
FROM `accts` 
WHERE  
	IF (
		INSTR('@', 'a@b') != 0, 
		`user` = 'a@b' AND `realm` IS NULL, 
		`email` = 'a@b'
	)

Open in new window

LVL 1
RegProctorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Terry WoodsConnect With a Mentor IT GuruCommented:
You're right - it's ok. Your problem is the order of the parameters passed to instr. This works for me on some test data:
       IF (
                INSTR('a@b','@') = 0,
                `user` = 'a' AND `realm` IS NULL,
                CONCAT_WS('@', `user`, `realm`) = 'a@b'
        )

Open in new window

0
 
RegProctorAuthor Commented:
Made a mistake copying the code over. Here it is fixed (although not working so not sure I should say "fixed").

The only difference is "!=" changed to "=".
#
# In both cases below consider all instances of "a@b" to also sometimes be of the form "a"
#
 
#
# Case 1: Does not return row in case of "a@b", does work in case of "a"
#
SELECT 
	`pwd` 
FROM `accts` 
WHERE  
	IF (
		INSTR('@', 'a@b') = 0, 
		`user` = 'a@b' AND `realm` IS NULL, 
		CONCAT_WS('@', `user`, `realm`) = 'a@b'
	);
 
#
# Case 2: Gives undefined column error
#
SELECT 
	`pwd`, 
	CONCAT_WS('@', `user`, `realm`) AS email
FROM `accts` 
WHERE  
	IF (
		INSTR('@', 'a@b') = 0, 
		`user` = 'a@b' AND `realm` IS NULL, 
		`email` = 'a@b'
	);

Open in new window

0
 
Terry WoodsIT GuruCommented:
Try this:
SELECT 
        `pwd`, 
        CONCAT_WS('@', `user`, `realm`) AS email
FROM `accts` 
WHERE  
        IF (
                INSTR('@', 'a@b') = 0, 
                `user` = 'a@b' AND `realm` IS NULL, 
                CONCAT_WS('@', `user`, `realm`) = 'a@b'
        );

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
snoyes_jwCommented:
You can't refer to an aliased column in the WHERE clause. You have three choices:

- Repeat the expression in the WHERE clause, like TerryAtOpus shows
- Use a HAVING clause instead of a WHERE clause to refer to filter those expressions
- Make the expression part of a subquery, as in SELECT email FROM (SELECT CONCAT_WS('@', `user`, `realm`) AS email FROM accts) AS dt WHERE email = blah...

The approach given by TerryAtOpus is probably best.
0
 
RegProctorAuthor Commented:
As given by Terry still the same problem, shown below:
Given the data:
 
user  realm  pwd
 a      b     c
 a     NULL   d
 
In the case of:
WHERE  
        IF (
                INSTR('@', 'a@b') = 0, 
                `user` = 'a@b' AND `realm` IS NULL, 
                CONCAT_WS('@', `user`, `realm`) = 'a@b'
        );
 
returns: 0 rows (should be 1 row)
 
And in the case of:
WHERE  
        IF (
                INSTR('@', 'a') = 0, 
                `user` = 'a' AND `realm` IS NULL, 
                CONCAT_WS('@', `user`, `realm`) = 'a'
        );
 
returns: 1 row (just as it should.)

Open in new window

0
 
Terry WoodsIT GuruCommented:
I think you've got the logic mixed up for the if condition in the first case. It should be:
WHERE  
        IF (
                INSTR('@', 'a@b'),
                `user` = 'a@b' AND `realm` IS NULL,
                CONCAT_WS('@', `user`, `realm`) = 'a@b'
        );
 
And the second case just doesn't make sense, given the data you've got. If your realm is NULL then shouldn't the user be "a@b" otherwise neither of your options in the "if" will return true?
0
 
RegProctorAuthor Commented:
       IF (
                INSTR('@', 'a') = 0,
                `user` = 'a' AND `realm` IS NULL,
                CONCAT_WS('@', `user`, `realm`) = 'a'
        );

Replacing 'a' with the placeholder 'ToFind':
        IF (
                INSTR('@', 'ToFind') = 0,
                `user` = 'ToFind' AND `realm` IS NULL,
                CONCAT_WS('@', `user`, `realm`) = 'ToFind'
        );

 In pseudo code:
        IF (
                'ToFind' does not have an '@' symbol then do this (assume that the realm is NULL):
                                `user` = 'ToFind' AND `realm` IS NULL,
                otherwise, do this (assume that the realm is NOT NULL but a value we want to match):
                                CONCAT_WS('@', `user`, `realm`) = 'ToFind'
        );

I just don't see a problem with this.
0
 
RegProctorAuthor Commented:
Duh! I can't believe I didn't see that!

Thanks.
0
All Courses

From novice to tech pro — start learning today.