Professional Web Applications Themes

Question on SELECT ... WHERE... sql statement - MySQL

Dear all, I would like to do some sql operations like "SELECT expr1 FROM table WHERE expr1 > expr2". Here, expr1 could be very long, so that I would like to simplify the statement as "SELECT expr1 AS some_symbol FROM table WHERE some_symbol > expr2" Unfortunately, the mysql server gives me the error - unknown column 'some_symbol'.... Could anyone tell if there is any way to simplify such query? Thank you so much. Best regards, Jacky....

  1. #1

    Default Question on SELECT ... WHERE... sql statement

    Dear all,

    I would like to do some sql operations like
    "SELECT expr1 FROM table WHERE expr1 > expr2".

    Here, expr1 could be very long, so that I would like to simplify the
    statement as
    "SELECT expr1 AS some_symbol FROM table WHERE some_symbol > expr2"

    Unfortunately, the mysql server gives me the error - unknown column
    'some_symbol'.... Could anyone tell if there is any way to simplify
    such query?

    Thank you so much.

    Best regards,
    Jacky.

    Jacky Guest

  2. #2

    Default Re: Question on SELECT ... WHERE... sql statement


    Jacky Yuk wrote: 

    HAVING

    strawberry Guest

  3. #3

    Default Re: Question on SELECT ... WHERE... sql statement

    "Jacky Yuk" <com> wrote in news:1164561775.261836.99390
    n67g2000cwd.googlegroups.com:
     

    All you're doing here is saying that your expr1 column should be called
    "some_symbol" when the results are given back to you.

    How can expr1 be very long? If you mean that 'expr1' is really an
    expression of sorts, well, the AS refers to a single column. Your
    change to the query here does nothing to simplify the statement or
    anything of the sort - you're just telling it to name the column expr1
    something else (some_symbol) when its returned to you.
     

    Again, I don't know how to 'simplify' your query any more than it is -
    it's pretty simple already.

    The reason you are getting the error is because of your alias... the
    results are not referred to as 'some_symbol' until they're already
    returned.... so what you want is:
    "SELECT expr1 AS some_symbol FROM table WHERE expr1 > expr2"

    As you can see, nothing about your query has changed at all except the
    'alias' of the column. This 'new' query is exactly the same as the
    original. It offers no enhanced value, and is certainly not
    'simplified' (one could argue that its less simple since you're
    introducing an alias).
    Good Guest

  4. #4

    Default Re: Question on SELECT ... WHERE... sql statement


    Good Man wrote: 
    >
    > All you're doing here is saying that your expr1 column should be called
    > "some_symbol" when the results are given back to you.
    >
    > How can expr1 be very long? If you mean that 'expr1' is really an
    > expression of sorts, well, the AS refers to a single column. Your
    > change to the query here does nothing to simplify the statement or
    > anything of the sort - you're just telling it to name the column expr1
    > something else (some_symbol) when its returned to you.

    >
    > Again, I don't know how to 'simplify' your query any more than it is -
    > it's pretty simple already.
    >
    > The reason you are getting the error is because of your alias... the
    > results are not referred to as 'some_symbol' until they're already
    > returned.... so what you want is:
    > "SELECT expr1 AS some_symbol FROM table WHERE expr1 > expr2"
    >
    > As you can see, nothing about your query has changed at all except the
    > 'alias' of the column. This 'new' query is exactly the same as the
    > original. It offers no enhanced value, and is certainly not
    > 'simplified' (one could argue that its less simple since you're
    > introducing an alias).[/ref]

    I don't see much wrong with wanting to simplify the query. Lets say the
    OP's trying
    to determine the radius of a circle from its chord and amplitude (or
    whatever the correct term is). Filtering the result set might not be
    the fastest way of getting to the answer but it might be less tedious
    than writing the formula twice:

    SELECT ((c1*c1)/(8*c1))+(c2/2) a,c2 b FROM `mytable` HAVING a < b;

    strawberry Guest

  5. #5

    Default Re: Question on SELECT ... WHERE... sql statement

    Thank you for the help. In fact, I am trying to use mysql to perform
    query on retrieving images based on image features which usually
    includes some simple matrix operations, and therefore, make the expr1
    very long. I can now simplify the construction by HAVING. Thank you
    very much^^.

    Best regards,
    Jacky

    strawberry gDG
     
    > >
    > > All you're doing here is saying that your expr1 column should be called
    > > "some_symbol" when the results are given back to you.
    > >
    > > How can expr1 be very long? If you mean that 'expr1' is really an
    > > expression of sorts, well, the AS refers to a single column. Your
    > > change to the query here does nothing to simplify the statement or
    > > anything of the sort - you're just telling it to name the column expr1
    > > something else (some_symbol) when its returned to you.
    > > 
    > >
    > > Again, I don't know how to 'simplify' your query any more than it is -
    > > it's pretty simple already.
    > >
    > > The reason you are getting the error is because of your alias... the
    > > results are not referred to as 'some_symbol' until they're already
    > > returned.... so what you want is:
    > > "SELECT expr1 AS some_symbol FROM table WHERE expr1 > expr2"
    > >
    > > As you can see, nothing about your query has changed at all except the
    > > 'alias' of the column. This 'new' query is exactly the same as the
    > > original. It offers no enhanced value, and is certainly not
    > > 'simplified' (one could argue that its less simple since you're
    > > introducing an alias).[/ref]
    >
    > I don't see much wrong with wanting to simplify the query. Lets say the
    > OP's trying
    > to determine the radius of a circle from its chord and amplitude (or
    > whatever the correct term is). Filtering the result set might not be
    > the fastest way of getting to the answer but it might be less tedious
    > than writing the formula twice:
    >
    > SELECT ((c1*c1)/(8*c1))+(c2/2) a,c2 b FROM `mytable` HAVING a < b;[/ref]

    Jacky Guest

Similar Threads

  1. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  2. SELECT statement
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 23
    Last Post: August 18th, 01:12 PM
  3. possible to run a select with an if statement in it?
    By Jim in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 17th, 04:48 PM
  4. Select Statement Question (Again)
    By Largo SQL Tools in forum Microsoft SQL / MS SQL Server
    Replies: 9
    Last Post: July 14th, 05:02 PM
  5. Select Statement Help Please
    By Bob in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 14th, 02:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139