Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-06-30
8
Medium Priority
?
355 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:RegProctor
  • 4
  • 3
8 Comments
 
LVL 1

Author Comment

by:RegProctor
ID: 24743593
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24750318
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
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 24751455
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 1

Author Comment

by:RegProctor
ID: 24752747
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24758441
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
 
LVL 1

Author Comment

by:RegProctor
ID: 24761018
       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
 
LVL 35

Accepted Solution

by:
Terry Woods earned 1400 total points
ID: 24768508
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
 
LVL 1

Author Closing Comment

by:RegProctor
ID: 31598270
Duh! I can't believe I didn't see that!

Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question