2

i am trying to pull data from sql with several conditions from a single column
how can i get this to work?

SELECT RIGHT(productID, 2) AS 'a', SUBSTRING(productID,1,2) AS 'b', productID 
FROM products 
WHERE `group`='$one' 
  AND `a` LIKE 'AA%' 
  AND `b` LIKE '$two'

i am trying to get the first 2 letters of the row and last 2 letters from the same row as well as checking if group=$one but get this error

    Unknown column 'b' in 'where clause'
Improve this question
4
  • 1
    So what exactly is your question? You haven't shown any data, described what it's supposed to produce, or anything else that we can use to go on. What's not working? What do you expect it to do that it isn't? Post sample data and the what you're trying to get as a result, and someone can help. We can't see your data or read your screen from here; you need to give us the information to help you. Commented Apr 17, 2012 at 2:12
  • Logic fails here for me.. right of productID for 2 positions will be only 2 positions long thus can't ever = 'AAA%' and I don't think strings start at position 0 1 for 2 would include 1 & 2 of product ID (though why not just use left) and you can't reference an aliased field in a where clause. you could either recalculate the values OR wrap it in a select or add a group by/having and use the alias in the having Commented Apr 17, 2012 at 2:14
  • ProductID isn't a 0 based index. Try SUBSTRING(productID,1,2) or LEFT(productID,2) since you already used right.... and you have an unmatched ) on right(productID,2) instead of right(productID),2) Commented Apr 17, 2012 at 2:21
  • revised the 0 to a 1 but still getting same error: Unknown column 'b' in 'where clause' Commented Apr 17, 2012 at 2:27

4 Answers 4

Reset to default
1 
SELECT RIGHT(productID, 2) AS 'a', SUBSTRING(productID,1,2) AS 'b', productID 
FROM products 
WHERE `group`='$one' 
GROUP BY productID
HAVING
  a = 'AA' 
  and b LIKE '$two'

No need for the like it's two positions % increases execution.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

im still having problems, does this get the last 2 characters of 'a'? RIGHT(productID), 2) AS 'a'
mySQL string manipulation functions Right(productID) will return the two right most characters in the PRODUCTID field for the record in question. substring(productID,1,2) will return the left TWO most characters in productID field. What's the New error or same error?
1

I created a simple table (sotest for StackOverFlow Test table) and added a VARCHAR column to it called col1. The code below worked for me.

SELECT RIGHT(col1, 2) AS a, SUBSTRING(col1,1,2) AS b, col1 as col FROM sotest  Having a like 'in%' and b like 'te%'

The return is as bellow

|  a |  b  |     col      |
|'in'| 'te'| 'test_jermin'|
Improve this answer

Comments

0 
SELECT RIGHT(productID), 2)
                      ^---extra bracket

you're terminating the function before you specify how many characters to extract.

Improve this answer

5 Comments

don't quote the aliases. just have it AS a and AS b. quoting things like that turns them into strings, and then they're not-aliases.
MySQL lets you reference alias fields in the where clause?
@xqbert: yes, it does. very much nicer than mssql/jet.
Wow Guess MySQL needs to update their docs then
xqbert: the only restriction is that the alias has to be on a non-aggregate result. you can't use a 'where' on (say) a count(*) as a, because the count value won't be availble at the time the where decision is being made.
0

That is because of your error in RIGHT function call, you have a misplaced parenthesis. Change the statement of SELECT to this:

SELECT RIGHT(productID, 2)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.