2012/10/11 13:46 -0400, Mark Haney
I know it's been a while since I wrote serious queries, but I'm sure I have
done something like this before:
SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id, machine.factory_id
FROM events JOIN machine ON events.mach_id = machine.mach_id W
Some places allow aliases, some don't. Some (GROUP BY, ORDER BY) even allow
ordinals.
For performance, the optimal index would be
INDEX(factory_id, date)
and then do
WHERE date >= "2012-10-11"
AND date < "2012-10-11" + INTERVAL 1 DAY
>
I know it's been a while since I wrote serious queries, but I'm sure I
have done something like this before:
SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id,
machine.factory_id FROM events JOIN machine ON events.mach_id =
machine.mach_id WHERE machine.factory_id = "1" AND vDate = "
Thanks, Rick!
I had forgotten all about variables for some strange reason...
The subselect isn't nearly as nice as variable, because I'd have to repeat it
several times.
On 5 Oct 12, at 20:19, mysql-digest-h...@lists.mysql.com wrote:
> From: Rick James
>
> One way:
>
> SELECT @foo := this
Wednesday, October 03, 2012 4:09 PM
> To: mysql@lists.mysql.com
> Subject: Accessing Column Aliases In Other Columns?
>
> I would like to refer to calculated columns in other columns. I thought
> a column alias would do it, but apparently they're only for
> aggregation, like GRO
I would like to refer to calculated columns in other columns. I thought a
column alias would do it, but apparently they're only for aggregation, like
GROUP BY.
Other than repeating the entire calculation, what techniques are available for
accessing such a calculation?
I'm calculating an electr
2011/08/08 10:25 -0400, Jerry Schwartz
I was a reluctant convert, and still don't use Hungarian notation consistently;
but in something like MS Access, where you might want to associate a label with
a field, things like "lblCompany" and "txtCompany" make a lot of sense.
I forg
2011/08/08 00:13 -0600, Mike Diehl
Well, I can see this being useful in assembly language, or strongly-typed,
non-OO languages. But I was asking specifically about SQL!
We know from context that customers is a table and it makes no sense at all to
prefix a type to it in order to
bytesmiths.com; mysql@lists.mysql.com
>Subject: RE: Hungarian Notation [Was Re: Too many aliases]
>
>
>Jan-
>the upside is you dont have to look up a variable to know what type it is:
>zVariable is Null termed string
>bVariable is boolean
>nVariable is an Integer
>fVariable is
On Saturday 06 August 2011 10:58:43 am Jan Steinman wrote:
> > From: Johnny Withers
> >
> > http://en.wikipedia.org/wiki/Hungarian_notation
Well, I can see this being useful in assembly language, or strongly-typed,
non-OO languages. But I was asking specifically about SQL!
When will this EVER
y
Cc: mysql@lists.mysql.com
Sent: Sun Aug 07 19:03:43 2011
Subject: Re: Hungarian Notation [Was Re: Too many aliases]
I despise this sort of notation, and have instead adopted what have
cheerfully named Hungarian Suffix notation, the reason being Signal-To-Noise
ratio. Instead of prefacing everything
I despise this sort of notation, and have instead adopted what have
cheerfully named Hungarian Suffix notation, the reason being Signal-To-Noise
ratio. Instead of prefacing everything with some form of prefix, just do the
opposite:
Customer_tbl
Customer_Dead_boo
Customer_DOB_date
Customer_qs (that
..Shawshank Redemption
> Subject: Hungarian Notation [Was Re: Too many aliases]
> From: j...@bytesmiths.com
> Date: Sat, 6 Aug 2011 09:58:43 -0700
> To: mysql@lists.mysql.com
>
> > From: Johnny Withers
> >
> > http://en.wikipedia.org/wiki/Hungarian_notation
>
>
It's simple... ttwwadi is the only reason I assume.
Sent from my iPad
On Aug 5, 2011, at 2:39 PM, (Hal�sz S�ndor) h...@tbbs.net wrote:
> 2011/08/04 10:21 -0500, Johnny Withers
> http://en.wikipedia.org/wiki/Hungarian_notation
>
>
> On Thu, Aug 4, 2011 at 9:41 AM, Mike Diehl
> From: Johnny Withers
>
> http://en.wikipedia.org/wiki/Hungarian_notation
"The original Hungarian notation... was invented by Charles Simonyi... who
later became Chief Architect at Microsoft."
Ugh. That explains a lot!
The only time I let types intrude on names is with booleans, which I try
2011/08/04 10:21 -0500, Johnny Withers
http://en.wikipedia.org/wiki/Hungarian_notation
On Thu, Aug 4, 2011 at 9:41 AM, Mike Diehl wrote
> Well, while we're on the subject of SQL style, can anyone tell me why I'm
> always seeing people prefixing the name of a table with something like
>
; Sent: Thursday, August 04, 2011 8:26 AM
> > To: r...@grib.nl
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Too many aliases
> >
> > >>>> 2011/08/03 12:46 +0200, Rik Wasmus >>>>
> >
> > But the
> > main thing is it helps
d.
>
> -Original Message-
> From: h...@tbbs.net [mailto:h...@tbbs.net]
> Sent: Thursday, August 04, 2011 8:26 AM
> To: r...@grib.nl
> Cc: mysql@lists.mysql.com
> Subject: Re: Too many aliases
>
> >>>> 2011/08/03 12:46 +0200, Rik Wasmus >>>>
>-Original Message-
>From: David Lerer [mailto:dle...@us.univision.com]
>Sent: Wednesday, August 03, 2011 10:25 AM
>To: mysql@lists.mysql.com
>Subject: RE: Too many aliases
>
>I rarely use aliases (unless rarely required in self-join queries).
>Yes, the column name
...@grib.nl
Cc: mysql@lists.mysql.com
Subject: Re: Too many aliases
>>>> 2011/08/03 12:46 +0200, Rik Wasmus >>>>
But the
main thing is it helps to distinguish tables in joins having the same
table
more then once (and of course results from subqueries etc.):
SELECT first.*
FROM t
d
ONfirst.some_id = second.some_id
AND first.id != second.id
WHERE second.id IS NULL
<<<<<<<<
Well, yes, here it is needful. But it seems to me from most of the examples
that people here post, that they have the idea that it is the style always to
use one-letter aliases, whe
On Aug 3, 2011 9:24 AM, "David Lerer" wrote:
>
> I rarely use aliases (unless rarely required in self-join queries).
> When I have that option, I create unique columns by prefixing every
> table (and its objects) with a number.
> Something like:
> Create table T1234_Em
I rarely use aliases (unless rarely required in self-join queries).
When I have that option, I create unique columns by prefixing every
table (and its objects) with a number.
Something like:
Create table T1234_Employee
(C1234_Employee_id number(5),
C1234_employee_status char(1)...)
Index
> 2011/08/02 12:11 +0530, Adarsh Sharma
>
> select p.* from table A p, B q where p.id=q.id
>
> or
>
> select p.* from table B q , A p where q.id=p.id
>
> Why do people constantly change table names for queries, although, as here,
> it gain them nothing? It often makes for less
2011/08/02 12:11 +0530, Adarsh Sharma
select p.* from table A p, B q where p.id=q.id
or
select p.* from table B q , A p where q.id=p.id
Why do people constantly change table names for queries, although, as here, it
gain them nothing? It often makes for less clarity (for which
On 9/27/2010 9:10 AM, Ramsey, Robert L wrote:
I have a query with three subselects, all referencing the same table. I'd like
to be able to combine them into one with aliases.
Here's what I have now:
select letter_codename,
(select greek from letter_otherlanguages where letter =&#x
nday, September 27, 2010 7:10 AM
To: [MySQL]
Subject: multiple aliases
I have a query with three subselects, all referencing the same table. I'd
like to be able to combine them into one with aliases.
Here's what I have now:
select letter_codename,
(select greek from letter_otherlangua
I have a query with three subselects, all referencing the same table. I'd like
to be able to combine them into one with aliases.
Here's what I have now:
select letter_codename,
(select greek from letter_otherlanguages where letter ='A') as greek,
(select french from letter_
Subject: Update Join with Aliases
>
> Hello.
>
> I am having a time trying to get this to work. I hope someone can help
> me wit the syntax!
>
> Here is the original query:
>
> UPDATE ambien_nev.Sections
> INNER JOIN ambien_nev.Sections ON natural_db.Sections.s
message: Not unique table/alias: 'Sections'
So I tried aliases:
UPDATE ambien_nev.Sections as SectionsSp
INNER JOIN SectionsSp ON natural_db.Sections.section_id = SectionsSp.section_id
SET SectionsSp.feature1 = natural_db.Sections.feature1,
SectionsSp.feature2 = natural_db.Section
CTED]
> Sent: Friday, October 19, 2007 9:04 PM
> To: mysql@lists.mysql.com
> Subject: Need ideas on handling aliases and a.k.a.s
>
> I'm trying to wrap my head around dealing with people in a table that
> have multiple names or akas.
>
> I run an entertainment news site and
e Last field and remember to always
use Longoria Parker when I input new info, but what happens if she gets
divorced?
Just wondering how some of you have handled akas/aliases/divorces for
things like customer databases. How do you ensure that a name change
doesn't actually cause a brand new re
er to always
use Longoria Parker when I input new info, but what happens if she gets
divorced?
Just wondering how some of you have handled akas/aliases/divorces for
things like customer databases. How do you ensure that a name change
doesn't actually cause a brand new record for the person if
hello
is it possible to make aliases for types, so that for instance:
"uint" means "int unsigned not null default 0"
it makes table definitions unreadable having to write those long
definitions all the time.
thanks!
--
MySQL General Mailing List
For list archives: ht
I concede to the MySQL engineer :-)
-Original Message-
From: Shawn Green [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 10, 2007 3:30 PM
To: Jonathan Langevin
Cc: Chris White; Richard Reina; mysql@lists.mysql.com
Subject: Re: Does Update allow for aliases?
Hi all,
Multi-table
HERE y.id IS NULL
Or the opposite with a multi-table update:
UPDATE x,y
SET x.YitemsExist="Y", y.XitemsExist="Y"
WHERE x.id=y.id
- Original Message -
From: "Chris White" <[EMAIL PROTECTED]>
To:
Sent: Wednesday, January 10, 2007 12:55 PM
Subject: Multiple tabl
The key part of the documentation for me was:
- Original Message -
From: "Chris White" <[EMAIL PROTECTED]>
To:
Sent: Wednesday, January 10, 2007 12:55 PM
Subject: Multiple table updates (Was: Does Update allow for aliases)
Reading the noted previous thread, I w
es Update allow for aliases?
I am trying to update from one table to another but I get a syntax error
when I try:
UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE
o.ID=a.ID;
If update does not support aliases, is there another way to do this query?
I am usin V3.23.54.
Hi all,
Multi-table updates are not possible for versions older than 4.0.4.
(http://dev.mysql.com/doc/refman/4.1/en/update.html) so the operation is
not possible with your current version.
To be complete, though, each of you missed the second syntax error in
his statement
Jonathan Langevin
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 10, 2007 12:10 PM
To: Richard Reina
Cc: mysql@lists.mysql.com
Subject: Re: Does Update allow for aliases?
Richard Reina wrote:
> I am trying to update from one table to another but I get a syntax
error when I try:
>
> UPD
Reading the noted previous thread, I was curious as to updating multiple
tables. I read the MySQL docs, which mentions that you can do it:
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] /|table_references|/
SET /|col_name1|/=/|expr1|/ [, /|col_name2|/=/|expr2|/ ...]
[WHERE /|where
Hi Richard,
Richard Reina wrote:
I am trying to update from one table to another but I get a syntax error when I
try:
UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID;
If update does not support aliases, is there another way to do this query? I am usin
Richard Reina wrote:
I am trying to update from one table to another but I get a syntax error when I
try:
UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID;
First off, it'd be best if possible (I know some cases prevent it) to
upgrade your server. The
I am trying to update from one table to another but I get a syntax error when I
try:
UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE
o.ID=a.ID;
If update does not support aliases, is there another way to do this query? I
am usin V3.23.54. Any help would be
Jochem van Dieten <[EMAIL PROTECTED]> wrote on 11/07/2005 03:32:25 PM:
> On 11/7/05, mos wrote:
> > Why isn't there a way to reference column aliases in the columns list
or
> > where clause?
>
> Because the SQL standad says so. See chapter 7 of ISO/IEC 9075-2:20
On 11/7/05, mos wrote:
> Why isn't there a way to reference column aliases in the columns list or
> where clause?
Because the SQL standad says so. See chapter 7 of ISO/IEC 9075-2:2003.
> select if(score<50,-5,0) failing_score, if(score>50, 1, 0) passing_score,
&g
> Why isn't there a way to reference column aliases in the columns list or
> where clause?
http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html
That's why.
--
Scott Noyes
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysq
Why isn't there a way to reference column aliases in the columns list or
where clause?
Example:
select if(score<50,-5,0) failing_score, if(score>50, 1, 0) passing_score,
attendance/totaldays Percent_Attendance ,
failing_score/passing_score*percent_attendance
from schoolwork
(
Does anyone know if there are any 3rd party shells that can connect to
a mysql server that support aliases, or have any features that can
make working from the console faster?
Thanks!
-Jacob
On Fri, 4 Mar 2005 11:59:56 -0600, jacob martinson
<[EMAIL PROTECTED]> wrote:
> Is it possible
Hello.
No. There are only short forms of each command. See:
http://dev.mysql.com/doc/mysql/en/mysql-commands.html
jacob martinson <[EMAIL PROTECTED]> wrote:
> Is it possible to setup aliases in the mysql console to cut down on
> typing, i.e. instead of having to ty
Is it possible to setup aliases in the mysql console to cut down on
typing, i.e. instead of having to type out "show tables" I can just
type "\dt" as in postgresql?
Thanks,
Jacob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
At 04:11 PM 2/26/2005, David Smithson wrote:
Hi all. Can somebody explain to me why the following SELECT doesn't work?:
SELECT COUNT(DISTINCT(CONCAT(contact_firstname,' ',contact_lastname)
as contact_name)) as count FROM Contacts GROUP BY contact_ident ORDER
BY contact_name;
The error returned is:
David Smithson wrote:
Hi all. Can somebody explain to me why the following SELECT doesn't work?:
SELECT COUNT(DISTINCT(CONCAT(contact_firstname,' ',contact_lastname)
as contact_name)) as count FROM Contacts GROUP BY contact_ident ORDER
BY contact_name;
The error returned is:
ERROR 1064: You have a
Hi all. Can somebody explain to me why the following SELECT doesn't work?:
SELECT COUNT(DISTINCT(CONCAT(contact_firstname,' ',contact_lastname)
as contact_name)) as count FROM Contacts GROUP BY contact_ident ORDER
BY contact_name;
The error returned is:
ERROR 1064: You have an error in your SQL
Hi all. Can somebody explain to me why the following SELECT doesn't work?:
SELECT COUNT(DISTINCT(CONCAT(contact_firstname,' ',contact_lastname)
as contact_name)) as count FROM Contacts GROUP BY contact_ident ORDER
BY contact_name;
The table has:
contact_ident
contact_firstname
contact_lastname
Hi,
For this only RDBMS came into being.
You keep your master data in a table with relevent descriptions,
codify the item. Use the code in other location. In qa web page always
search against/show the full desc of what others require. Add a
category like school/university/pre-matric etc. this coul
Don't think "file" think "table"! ;-)
CREATE TABLE universitysynonyms (
ID int auto_increment primary key,
synonym varchar(40) not null,
university_id int not null,
UNIQUE(Synonym, university_id)
)
Each time you run into something you don't have in your "universit
I need help on the best way to handle a field that could have many
different ways of naming something.
For instance, school name
Let's take Saint Joseph's University
This could be Saint Joseph's Univeristy, U. of St. Joe, SJU, Univ. St.
Joe, etc...
In this case, I don't think I can always get w
Hi!
On Oct 05, Ville Mattila wrote:
> Hi there,
>
> I have noticed a few things that cause problems when using table aliases
> (SELECT ... FROM table1 t1, table2 t2):
>
> 1) Fulltext index queries don't work. I tried to complete a following query:
>
> a)
> SE
Hi there,
I have noticed a few things that cause problems when using table aliases
(SELECT ... FROM table1 t1, table2 t2):
1) Fulltext index queries don't work. I tried to complete a following query:
a)
SELECT p.*, c.name AS categoryname FROM products p, categories c WHERE
MATCH(p
This is expected behavior for MySQL post version 4.1. Check out:
http://dev.mysql.com/doc/mysql/en/DELETE.html
particularly the 'Note:' section just before the user comments. If you
use table aliases you must use those aliases to refer to the tables you
want to delete, not the t
>Description:
Multi-delete stops working in all forms when used along with table
aliases, it gives an error saying
ERROR 1109 (42S02): Unknown table 'test_base' in MULTI DELETE
Which definately is NOT the case. After trying all kinds of
combinations I reli
Scott Haneda <[EMAIL PROTECTED]> wrote:
> There are some things I do in mysql often, is there some way to alias or
> otherwise bookmark a a sql statement for simple rapid use in the future?
You can put SQL statements to the file and then execute them from the file.
--
For technical support con
There are some things I do in mysql often, is there some way to alias or
otherwise bookmark a a sql statement for simple rapid use in the future?
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com
> -Original Message-
> From: Chris [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, December 17, 2003 8:12 AM
> To: [EMAIL PROTECTED]
> Subject: RE: Issues with count(), aliases, and LEFT JOINS
>
> Try changing it to this:
>
> ...
> -> COUNT(DISTI
Original Message-
From: Brandon Ewing [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 17, 2003 1:59 AM
To: [EMAIL PROTECTED]
Subject: RE: Issues with count(), aliases, and LEFT JOINS
> -Original Message-
> From: Brandon Ewing [mailto:[EMAIL PROTECTED]
> Sent: Monday, Dec
> -Original Message-
> From: Brandon Ewing [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 15, 2003 2:28 AM
> To: [EMAIL PROTECTED]
> Subject: Issues with count(), aliases, and LEFT JOINS
>
> Greetings,
>
> This is driving me crazy.
>
> I'm ru
Greetings,
This is driving me crazy.
I'm running MySQL 4.0.15-standard.
I've got a db that tracks switches, servers they connect to, and connections
between switches.
Schema for server:
+--+
--+
| Field|
If you can do this:
SELECT
table_a_alias.col_name
FROM
table_a table_a_alias;
Why can't you do this:
SELECT
1 AS A,
A + 1 AS B,
B + 1 AS C;
Why can't you use column aliases later in the select? When
you can use table aliases even before they are defined.
thnx,
Chris
lto:[EMAIL PROTECTED]
Sent: Monday, November 10, 2003 7:34 PM
To: [EMAIL PROTECTED]
Subject: Re: Aliases
what if we use the function at the right side of the equation?
such as
select anything
from table1, table2
where table1.id=left(table2.id,somenumber)
having both id in table were indexed
dont yo
-
From: Erik Osterman
To: [EMAIL PROTECTED]
Sent: Wednesday, November 12, 2003 3:17 AM
Subject: RE: Aliases
> From: Matt W [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 10, 2003 5:47 PM
> To: Erik Osterman; [EMAIL PROTECTED]
> No, Roger's method can't use
> From: Matt W [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 10, 2003 5:47 PM
> To: Erik Osterman; [EMAIL PROTECTED]
> No, Roger's method can't use an index. :-) But yes, using WHERE is
> better than HAVING.
Ah... right indeed. In this case it wouldn't work. My mistake... :)
> To get the
#x27;;
And I would guess that LIKE will be faster than the LEFT() function even
if there is no index.
Matt
- Original Message -
From: "Erik Osterman"
Sent: Tuesday, November 11, 2003 6:39 AM
Subject: RE: Aliases
However, you can use HAVING. HAVING is post-processed, in a br
Regards,
Erik Osterman
-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 09, 2003 6:59 AM
To: [EMAIL PROTECTED]
Cc: David Katz
Subject: Re: Aliases
* David Katz
> I am trying to do a select statement where I am using one of the
> aliases in the
* David Katz
> I am trying to do a select statement where I am using one of the
> aliases in the where clause. I keep getting an error that the field
> does not exist.
>From the manual: "Note that standard SQL doesn't allow you to refer to an
alias in a WHERE clause. Th
I am trying to do a select statement where I am using one of the aliases in
the where clause. I keep getting an error that the field does not exist.
example:
Select invno, invdate, invamt, left(invdate,2) as month from salesfile where
month = '01'
MySql keeps telling me that mont
I hope I've subscribed to the right list.
I am using version 3.23.41 of MySQL, and I'm having a problem getting
correct (expected?) behavior with column aliases.
I have a database A which has, among other things, an id field,
something like this:
CREATE TABLE a {
somedata VARCHAR(10
No, I hadn't. So I did. IT WORKED!
Thank you very much!
Roger Baklund wrote:
* [EMAIL PROTECTED]
[...]
In other words, any pix that has a bigpix defined is ok. But on any
pix that does not have a bigpix defined, I "loose" the filename info
from pix.
And I don't understand why!
Here is the
* [EMAIL PROTECTED]
[...]
> In other words, any pix that has a bigpix defined is ok. But on any
> pix that does not have a bigpix defined, I "loose" the filename info
> from pix.
>
> And I don't understand why!
>
> Here is the second query:
>
> select
> pix_section.code,pix.filename,pix.id,pix_use.
I have a database defined with the following tables:
CREATE TABLE pix (
id int(11) NOT NULL auto_increment,
propertyid int(11) NOT NULL default '0',
filename varchar(25) default NULL,
caption varchar(50) default NULL,
width smallint(4) unsigned NOT NULL default '0',
height smallint(4)
Hello. I was adviced to write to you about this from an experiensed mySQL
user, as this might be a bug in 4.0.0
Im trying to do this:
select col1,col2,col3,'text' as name from tabel where blahblah
now, this would add 'text' to all rows in the result, and that works just
fine
but, if i try with '
Hello. I was adviced to write to you about this from an experiensed mySQL
user, as this might be a bug in 4.0.0
Im trying to do this:
select col1,col2,col3,'text' as name from tabel where blahblah
now, this would add 'text' to all rows in the result, and that works just
fine
but, if i try with '
Is there a way to have MySQL label columns returned
from a select query with the complete column name, in
table.column format?
So if a column name is id, which is in the main table,
can MySQL return main.id instead of just id.
Sure I could alias each column, but if there is 30
columns, that's
83 matches
Mail list logo
