Simple Where clause question

[aclambert]

Hi Guys

apologies if this is a newbie question.

I've written a simple select statement as follows:

select CONCAT(firstNames,' ' , lastName) as 'fullname' from Customers

the query works fine and lists correctly. I have a name which shows as Joe Bloggs (as well as others) I then try to add the following:

where 'fullname' = 'Joe Bloggs'

my list is then blank even though I know I have a firstName of Joe and a lastName of Bloggs. I've tried 'like' instead of '=' and its no different.

I'm sure its something straight-forward, what am I missing?

Thanks in advance.

Aaron

 

[DonQuichote]

A WHERE-clause is meant to select something: you can only give field names and conditions here. If you want to filter after the record selection, use a HAVING clause.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[towerbase]

Try removing the quote characters from 'fullname'. Your SELECT statement should look something like

SELECT CONCAT(firstNames,' ' , lastName) AS fullname
FROM customers
WHERE fullname = 'Joe Bloggs'

Your original SQL statement was actually syntactically correct SQL but as the string 'fullname' is not equal to the string 'Joe Bloggs' no rows will ever get selected.

Andrew
Hampshire, UK

 

[r937]

actually, andrew, that won't work -- you can't use a column alias in the WHERE clause

this, strangely enough, will --

SELECT CONCAT(firstNames,' ',lastName) AS fullname
FROM customers
HAVING fullname = 'Joe Bloggs'

however, my preference is not to use HAVING (it's a bit of a hack, in my opinion)

instead, use a derived table, which then allows the use of a WHERE clause again

SELECT *
  FROM ( SELECT CONCAT(firstNames,' ',lastName) AS fullname
           FROM customers ) AS dt
 WHERE fullname = 'Joe Bloggs'

r937.com | rudy.ca

 

[johnwm]

.. or test for both names

SELECT CONCAT(firstNames,' ' , lastName) AS fullname
FROM customers
WHERE firstNames = 'Joe' AND lastName = 'Bloggs'

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[aclambert]

Hi Everyone

thanks for all the great advice. I'm still trying to learn so I'll take on board your comments about this being a 'hacky' way round things.

Thanks!

A.

 

[DonQuichote]

I think a HAVING clause is far less of a hack than a SELECT * FROM subselect.

Again, WHERE clauses are meant to directly limit the records to be selected. WHERE clauses do not need to drive indexes, but they do if they exist. If you need to filter on calculated values, you may need to do so in a HAVING clause.

That said, a WHERE clause (if possible) is preferable because more optimization is possible.

An example:
Suppose you have a table with coordinates (X and Y) and you want all the records within a radius R of a given point Xgiven, Ygiven. You could do so in a HAVING clause:

SELECT * FROM Coordinates
       HAVING (X-Xgiven)*(X-Xgiven) + (Y-Ygiven)*(Y-Ygiven) < R*R;

But this can only be solved by going over every row, regardless of any indexes for the X and Y columns.

A better query would be:

SELECT * FROM Coordinates
      WHERE X BETWEEN Xgiven-R AND Xgiven + R
        AND Y BETWEEN Ygiven-R AND Ygiven + R
      HAVING (X-Xgiven)*(X-Xgiven) + (Y-Ygiven)*(Y-Ygiven) < R*R;

What is the difference? Not the outcome. That should be the same. But the amount of rows to be checked is now reduced as the indexes on the X and/or Y column can be used to keep the really far records out of the calculation. So the difference in speed can be enormous.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)