2017/09/19 17:19 ... Don Wieland:
Of these found rows, I want to omit those rows where there are rows found after
the END TimeStamp based on ?below ?where clause:
WHERE 1 AND apt.appt_status_id IN (16) AND apt.user_id IN (3) AND apt.time_start
> ‘1504238399'
We are trying to fin
r ON r.room_id = apt.room_id
JOIN tl_users u ON u.user_id = apt.user_id
WHERE 1 AND apt.appt_status_id IN (16) AND apt.user_id IN (3) AND
apt.time_start BETWEEN '150156' AND '1504238399'
GROUP BY c.client_id
HAVING count(*) > 0
ORDER BY u.first_name, u
gt;>>> IFNULL(f_tag_bottom_minor_axis,
>>>>>>>> IFNULL(f_tag_ch_x_bottom,
>>>>>>>>IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
>>>>>>>>STDDEV
h_x_bottom, f_tag_ch_y_bottom) as ftag,
STDDEV(ch_x_top)
As Johnny Withers points out, you may repeat the expression in the
WHERE-clause:
WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) =
'E
FNULL(f_tag_ch_y_bottom, NULL) as ftag,
>>>>>> STDDEV(ch_x_top)
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Of course, this isn't your real problem, but you could use COAL
may repeat the expression in the
WHERE-clause:
WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7'
If really only one of those is not NULL, it is equivalent to this:
'E-CD7' IN (f_tag_b
> SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,
>>> STDDEV(ch_x_top)
>>>
>>>
>>> As Johnny Withers points out, you may repe
tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,
STDDEV(ch_x_top)
As Johnny Withers points out, you may repeat the expression in the
WHERE-clause:
WHERE COALESCE(f_tag_bottom, f_tag_bottom_
one):
> SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,
> STDDEV(ch_x_top)
> ....
>
> As Johnny Withers points out, you may repeat the expression in the
> WHERE-clause
STDDEV(ch_x_top)
As Johnny Withers points out, you may repeat the expression in the
WHERE-clause:
WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7'
If really only one of those is not NULL,
On Mon, Jan 25, 2016 at 9:32 PM, Larry Martell
wrote:
> On Mon, Jan 25, 2016 at 8:26 PM, Johnny Withers
> wrote:
> > You should probably turn this into a UNION and put an index on each
> column:
> >
> > SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_t
On Mon, Jan 25, 2016 at 8:26 PM, Johnny Withers wrote:
> You should probably turn this into a UNION and put an index on each column:
>
> SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom =
> 'E-CD7'
> UNION ALL
> SELECT f_tag_ch_x_bottom
On Mon, Jan 25, 2016 at 8:01 PM, Rebecca Love wrote:
> Have you tried using a select case statement for ftag?
How would that help? Unless I'm missing something, I'd still have to
have a reference to the column alias in the where clause.
>> On Jan 25, 2016, at 6:39 PM, L
You should probably turn this into a UNION and put an index on each column:
SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom =
'E-CD7'
UNION ALL
SELECT f_tag_ch_x_bottom AS ftag FROM data_cst WHERE f_tag_ch_x_bottom =
'E-CD7'
UNION ALL
SELECT f_tag_botto
Have you tried using a select case statement for ftag?
> On Jan 25, 2016, at 6:39 PM, Larry Martell wrote:
>
> On Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald wrote:
>>
>>
>> Am 26.01.2016 um 01:16 schrieb Larry Martell:
>>>
>>> I know I ca
On Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald wrote:
>
>
> Am 26.01.2016 um 01:16 schrieb Larry Martell:
>>
>> I know I cannot use an alias in a where clause, but I am trying to
>> figure out how to achieve what I need.
>>
>> If I could have an alias in a w
Am 26.01.2016 um 01:16 schrieb Larry Martell:
I know I cannot use an alias in a where clause, but I am trying to
figure out how to achieve what I need.
If I could have an alias in a where clause my sql would look like this:
SELECT IFNULL(f_tag_bottom,
IFNULL
I know I cannot use an alias in a where clause, but I am trying to
figure out how to achieve what I need.
If I could have an alias in a where clause my sql would look like this:
SELECT IFNULL(f_tag_bottom,
IFNULL(f_tag_bottom_major_axis,
IFNULL
Found it (after some days of searching):
https://code.launchpad.net/ubuntu/+source/mysql-workbench must be the
official repository according to
https://dev.mysql.com/doc/refman/5.7/en/installing-development-tree.html.
-Kalle
Am 15.10.2015 um 22:11 schrieb Karl-Philipp Richter:
> Hi,
> The only li
Hi,
The only list of mysql-related SCM repositories I found was
https://github.com/mysql which didn't contain a repository for
`mysql-workbench`. I only found the source tarball at
http://dev.mysql.com/downloads/workbench/. The development section at
http://dev.mysql.com/downloads/workbench/ doesn'
Hello Steve,
To what list should I post with a post-installation config and startup
> question?
>
>
This list, the MySQL General Mailing List, is the right place if the
question is about MySQL!
Cheers
--
Claudio
To what list should I post with a post-installation config and startup
question?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
d the specific case I mentioned, my goal is also to get a general
discussion about theorical behaviour of MySQL while handling WHERE clauses.
I don't have access to the application for now, but will try EXPLAIN as
soon as I can .
Here is the Result of Explain :
http://perso.stux6.net/mysq
mentioned, my goal is also to get a general
> discussion about theorical behaviour of MySQL while handling WHERE clauses.
>
> I don't have access to the application for now, but will try EXPLAIN as
> soon as I can .
Here is the Result of Explain :
http://perso.stux6.net/mysql/201
like so:
select into DATE_SUB(NOW(), INTERVAL 24 HOUR) INTO @yesterday ;
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate < @yesterday);
Indeed ! it can be a significant performance improvement ;).
Thanks for
while handling WHERE clauses.
I don't have access to the application for now, but will try EXPLAIN as
soon as I can .
Regards,
Christophe.
Le 19/03/2014 17:12, Morgan Tocker a écrit :
> Hi Harald,
>
>> you can call me Harald
>
> My apologies!
>
>> the question
into DATE_SUB(NOW(), INTERVAL 24 HOUR) INTO @yesterday ;
>
> SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
> ('PENDING', 'ACCEPTED') AND SubscribeDate < @yesterday);
>
Indeed ! it can be a significant performance improvement ;).
Thanks for this .
On 3/19/2014 7:27 PM, Mogens Melander wrote:
G'morning
A function in a where equals what ?
On Wed, March 19, 2014 15:05, Morgan Tocker wrote:
Hi Christophe,
Considering the following simple query :
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING&#
G'morning
A function in a where equals what ?
On Wed, March 19, 2014 15:05, Morgan Tocker wrote:
> Hi Christophe,
>
>> Considering the following simple query :
>>
>> SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
>> ('PENDING',
Am 19.03.2014 16:27, schrieb Morgan Tocker:
> Reindl,
you can call me Harald
>>> The order of the WHERE clause does not matter
>>
>> that is simply not true
>>
>> otherwise that documentation would not exist
>> http://dev.mysql.com/doc/refman/5.5/en/
Reindl,
>> The order of the WHERE clause does not matter
>
> that is simply not true
>
> otherwise that documentation would not exist
> http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html
Yes, the order of the columns in composite indexes *does matter*.
Am 19.03.2014 15:05, schrieb Morgan Tocker:
> When a MySQL server receives a query, it goes through a process called query
> optimization
> and tries to determine the best way to execute it (based on availability of
> indexes etc)
>
> The order of the WHERE clause does n
Hi Christophe,
> Considering the following simple query :
>
> SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
> ('PENDING', 'ACCEPTED') AND SubscribeDate < DATE_SUB(NOW(), INTERVAL 24
> HOUR);
>
> Which of these filters are proc
> From: Christophe
> To: mysql@lists.mysql.com
> Cc:
> Sent: Tuesday, 18 March 2014, 19:10
> Subject: Precedence in WHERE clauses.
>
> Hi list,
>
> I'd like to get your advice about precedence in where clauses in MySQL
> (5.0.51, and 5.1.66 in this c
suggest you temporarily memoize that like so:
select into DATE_SUB(NOW(), INTERVAL 24 HOUR) INTO @yesterday ;
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate < @yesterday);
On Tue, Mar 18, 2014 at 3:10 PM, Chris
Hi list,
I'd like to get your advice about precedence in where clauses in MySQL
(5.0.51, and 5.1.66 in this case / from lenny and squeeze Debian
packages ).
Considering the following simple query :
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING',
Awesome! Thank you very much Claudio! :)
On Thu, Nov 21, 2013 at 10:40 AM, Claudio Nanni wrote:
> Hi Jopoy,
>
> Try this:
>
> "SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE
> EXTRACT(YEAR_MONTH FROM acctstarttime) >= EXTRACT(YEAR_MONTH FROM
Hi Jopoy,
Try this:
"SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE
EXTRACT(YEAR_MONTH FROM acctstarttime) >= EXTRACT(YEAR_MONTH FROM
CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) <
EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP
otal_usage FROM radacct WHERE
EXTRACT(YEAR_MONTH FROM acctstarttime) >= EXTRACT(YEAR_MONTH FROM
CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) <
EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username
ORDER BY total_usage DESC;"
I wanted to add something
Even if you could block them, they would be easy to get around:
SELECT * FROM tbl WHERE 1;
If you have long running queries, you should investigate the reasons (other
than lack of WHERE).
* MyISAM locks the table for any writes. This prevents a SELECT from starting
or a select can prevent
age-
> From: Martin Gainty [mailto:mgai...@hotmail.com]
> Sent: 24 September 2012 04:58
> To: fuller.art...@gmail.com; pownall...@gmail.com
> Cc: mysql@lists.mysql.com
> Subject: RE: How to block SELECT * FROM table; but not SELECT * FROMT
> table WHERE...;
>
>
> Possi
Possibly run your constructed query thru a regex expression e.g.
String mydata = "SELECT * from table WHERE a Date: Sun, 23 Sep 2012 18:38:58 -0400
> Subject: Re: How to block SELECT * FROM table; but not SELECT * FROMT table
> WHERE...;
> From: fuller.art...@gmail.com
> To: pow
Tim,
I think you misunderstood the question. Daniel wants to block Select
queries that ask for all rwows, and permit only queries that ask for some
rows, as restricted by the Where clause.
Unfortunately, I don't think that can be done. But I'm not certain of that;
there might be a tric
that does not change the fact that it is valid to NOT have a where
statement and for me it sound plain stupid to think about blocking
queries without where-statements - the application developers will
hopefully kill any admin who finds a solution for this...
* i maintain a cms-system
select * from table where column=value means it will return only rows that
match. as long as you have proper indexing there should not be any issues.
On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz <
luis.daniel.lu...@gmail.com> wrote:
> Helo,
>
> Just wondering if
Am 23.09.2012 20:23, schrieb Luis Daniel Lucio Quiroz:
> Just wondering if is possible to block SELECT queries that doesnt have
> a WHERE statement within.
no and the idea is broken by design
what is wrong with a "select * from table" with small
tbales having only a handful
Helo,
Just wondering if is possible to block SELECT queries that doesnt have
a WHERE statement within.
LD
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Hi,
you have to use `HAVING' instead of `WHERE' like this.
SELECT DISTINCT
`term`,
COUNT(*) AS count
FROM blp_sql_distinct_temp_table
GROUP BY `term`
HAVING count >= 5
ORDER BY count DESC;
put `HAVING' next of `GROUP BY'.
`WHERE' behaves at before aggregate of `GR
Hi, I'm trying to get this work;
SELECT distinct `term`,count(*) as count FROM
blp_sql_distinct_temp_table where count >= 5 group by `term` order by
count DESC
But I get this error;
Unknown column 'count' in 'where clause'
How do I get only those records who
On Thu, Feb 23, 2012 at 5:50 PM, Daevid Vincent wrote:
> Anyone have any thoughts on how I might optimize this query?
As always, it's all about the indexes. The index it chose on your
main table looks pretty weak. You probably should move those
non-joining columns out of your join condition in
- Original Message -
> From: "Daevid Vincent"
>
> d.`date_release`!='-00-00' AND sd.`scene_id`=s.`scene_id` GROUP
> BY sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30;
Could you put an index on sg.scene_id? Not sure, but it might help the grouping
be more effici
scenes_genres` AS sg, `scenes_downloads_new` AS sd WHERE sg.`genre_id` IN
('1','8','10','19','38','58','65') AND d.`dvd_id`=s.`dvd_id` AND
sg.`scene_id`=s.`scene_id` AND d.`status`='ok' AND
d.`date_release`!='-00-00'
I also want to say Thank you Shawn for your valuable contribution. On the
note on Resumes -I thought I would put it out here. My company is looking
for an excellent MySQL DBA (Oracle and MSSQL experience a plus) Please send
me your resume.
Thanks!
Sabika
On Wed, Oct 19, 2011 at 12:30 PM, Shawn Gr
On 10/19/2011 13:29, Michael Dykman wrote:
While we have him online, I think we could all take a moment and be grateful
for the contributions of Shawn Green.
When I see the Oracle-bashing on this list, I am often reminded that we
still have a hard-core MySQL developer who has survived the ride t
Thanks Shawn!
Ninus from Montreal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
On Wed, Oct 19, 2011 at 12:29 PM, Michael Dykman wrote:
> While we have him online, I think we could all take a moment and be
> grateful
> for the contributions of Shawn Green.
>
> When I see the Oracle-bashing on this list, I am often reminded that we
> still have a hard-core MySQL developer who
While we have him online, I think we could all take a moment and be grateful
for the contributions of Shawn Green.
When I see the Oracle-bashing on this list, I am often reminded that we
still have a hard-core MySQL developer who has survived the ride to Sun and
again to Oracle who is still provid
On Sat, Sep 10, 2011 at 01:48, Carsten Pedersen wrote:
> `userTable.userid` => `userTable`.`userid`
>
Thank you Carsten. That was indeed the problem! Have a peaceful weekend.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http:/
le`
->ON `userTable.userid`=`anotherTable.userid`
-> SET `userTable.someField`="Jimmy Page"
-> WHERE `userTable.someField`="Jim Morrison"
->AND `anotherTable.date`< NOW();
ERROR 1054 (42S22): Unknown column 'userTable.someField
Now that I've got the syntax right, MySQL is complaining that a field
does not exist, which most certainly does:
mysql> UPDATE
-> `userTable`
-> INNER JOIN `anotherTable`
-> ON `userTable.userid`=`anotherTable.userid`
-> SET `userTable.someField`="
I'm trying to update on an join, but I can't find my error:
UPDATE `userTable`
SET `someField`="Jimmy Page"
FROM `userTable` INNER JOIN `anotherTable`
ON `userTable.userid`=`anotherTable.userid`
WHERE `userTable.someField`="Jim Morrison"
AND `anotherTable.dat
> Is it ALWAYS possible to fabricate a query/schema in
> such a way that MySQL ALWAYS uses the ideal
No. Optimisation is better in 5.6 than in 5.0, though.
Did you try adding multi-column indexes to cover the join and the order
by clause?
> 'Using where' extra -- you just
I sent this Friday, but it never made it to the list?!
-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com]
Sent: Friday, May 27, 2011 12:27 PM
To: mysql@lists.mysql.com
Subject: Using where; Using temporary; Using filesort
I'm trying to optimize a query that doesn&#
s 2011/05/27 12:26 -0700, Daevid Vincent
[a] the EXTENDED keyword doesn't seem to do anything different? I get the
same columns and results??!
"show warnings"
2011/05/27 12:26 -0700, Daevid Vincent
In other words, am I wasting my time trying to tweak my
query and indexe
status,status_release
key: status_release
key_len: 1
ref: const
rows: 1976
Extra: Using where; Using temporary; Using filesort
*** 2. row ***
id: 1
select_type: SIMPLE
table: s
type:
sql
>
> When creating a new database, a folder (with the name of the databas) gets
> created in this location:
> /opt/mysql/data
>
> Is there any way to change the location of where data is stored?
> The database that I need to create is going to have over a billion records
a folder (with the name of the databas) gets
> created in this location:
> /opt/mysql/data
>
> Is there any way to change the location of where data is stored?
> The database that I need to create is going to have over a billion records
> in it,
> so it needs to be in a speci
older (with the name of the databas)
> gets created in this location:
> /opt/mysql/data
>
> Is there any way to change the location of where data is stored?
> The database that I need to create is going to have over a billion
> records in it,
> so it needs to be in a specific
Add:
datadir=/path/to/datadir/mysql
to your my.cnf file and restart mysql.
-Original Message-
From: Tina Matter [mailto:ti...@umich.edu]
Sent: Friday, May 13, 2011 8:22 AM
To: mysql@lists.mysql.com
Subject: URGENT: Change Default Location of where Database Files get written?
I have a
mysql/data
Is there any way to change the location of where data is stored?
The database that I need to create is going to have over a billion
records in it,
so it needs to be in a specific place.
I want the database folder to get created here:
/science/databases/databasename
Thanks for any
>>>> 2011/04/26 17:55 +0300, Andre Polykanine >>>>
Aha. So, I should write
SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId`
instead of my original WHERE clause?
Thanks!
<<<<<<<<
I think so.
--
MySQL General Mailin
On 04/26/11 05:32, Halász Sándor wrote:
2011/04/25 18:45 +, Larry McGhaw
CREATE VIEW `myview2` AS
SELECT a.*, IF(b.`Name` IS NULL, '', b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
Well, for this construct
IF(b.`N
Hello Halбsz,
Aha. So, I should write
SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId`
instead of my original WHERE clause?
Thanks!
--
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http
I would go with join rather than where condition.
2011/4/26 Halász Sándor
> >>>> 2011/04/25 17:42 +0300, Andre Polykanine >>>>
> Here is the first one.
> We have two queries:
> SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
> `Blogs`.`Us
>>>> 2011/04/25 17:42 +0300, Andre Polykanine >>>>
Here is the first one.
We have two queries:
SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
`Blogs`.`UserId`=`Users`.`Id`;
and the following one:
SELECT `Blogs`.* FROM `Blogs`, `Users`
2011/04/25 18:45 +, Larry McGhaw
CREATE VIEW `myview2` AS
SELECT a.*, IF(b.`Name` IS NULL, '', b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
Well, for this construct
IF(b.`Name` IS NULL, '', b.`Name`)
there is a spe
outer join). This
particular set of circumstances
seems to cause MySQL to treat the resulting expression as "unknown" when used
in a where clause on the view
itself - such that any use of the expression in the where clause will evaluate
to unknown/false.
As a workaround - thi
particular set of circumstances
seems to cause MySQL to treat the resulting expression as "unknown" when used
in a where clause on the view
itself - such that any use of the expression in the where clause will evaluate
to unknown/false.
As a workaround - this view for example behaves a
> questions, please bear with me.
> Here is the first one.
> We have two queries:
> SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
> `Blogs`.`UserId`=`Users`.`Id`;
> and the following one:
> SELECT `Blogs`.* FROM `Blogs`, `Users`
need all blogs, even those without a matching user (can that
even occur?), the first one is better.
I prefer to write the INNER JOIN out though because it leaves my WHERE
clause to do filtering.
JW
On Mon, Apr 25, 2011 at 9:42 AM, Andre Polykanine wrote:
> Hello everyone,
> Sorry for
ing stupid
questions, please bear with me.
Here is the first one.
We have two queries:
SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
`Blogs`.`UserId`=`Users`.`Id`;
and the following one:
SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE
`Blogs`.`UserId`=`
'where' is a filter. You're limiting records based on a criterion.
'on' is used for joining.
On Mon, Apr 25, 2011 at 10:42 AM, Andre Polykanine wrote:
> Hello everyone,
> Sorry for my beginner question. Actually I have been using MySql for a
> long  time  b
queries:
SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
`Blogs`.`UserId`=`Users`.`Id`;
and the following one:
SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE
`Blogs`.`UserId`=`Users`.`Id`;
1. Are they identical?
2. Which is better (faster, more optimal
1 |
++--+--+--+--+
1 row in set (0.00 sec)
But when referenced in the where clause in any manner, no results are returned.
mysql> SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview` where
TYPE
NAME IS NOT NULL;
Empty set (0.00 sec)
-Original M
ID of entries in mytable (customers, say)
to IDs of types, which doesn't make much sense to me. Or did I get this
wrong? (As I said, I'm more of less learning-by-doing and no expert!)
Yours,
Daniel
Now your select queries will give results as follows:
mysql> SELECT COUNT(*
will give results as follows:
mysql> SELECT COUNT(*) FROM `myview2` WHERE `TypeName` LIKE '%';
+--+
| COUNT(*) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM `myview2` WHERE `TypeName` IS NOT NULL;
+--+
| COUNT(*) |
+---
On 22.04.2011 22:41, Larry McGhaw wrote:
It does appear to be some type of bug to me.
I agree. I was thrown by Daniels "first and third" comment, which I
guess should read "second and third"
I reproduced the behavior in 5.1.53-community on Windows.
/ Carsten
--
MySQL General Mailing List
F
ULL | TypeName IS NOT NULL |
++--+--+--+--+
| 1 | NULL | |0 |1 |
++--+--+--+--+
1 row in set (0.00 sec)
But when referenced in the where clause in
LIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `Ty
ING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS N
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*)
> -Original Message-
> From: Daniel Kraft [mailto:d...@domob.eu]
> Sent: Friday, April 22, 2011 12:37 PM
> To: mysql@lists.mysql.com
> Subject: WHERE does not work on calculated view field
>
> Hi all,
>
> I'm by no means a (My)SQL expert and just gett
T
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT CO
Thanks, but how can I pass the current users value from the other query ?
On Thu, Feb 3, 2011 at 4:22 PM, Simcha Younger wrote:
> On Thu, 3 Feb 2011 13:55:36 +
> Tompkins Neil wrote:
>
> > SELECT DISTINCT(away_teams_id) AS teams_id
> > FROM fixtu
On Thu, 3 Feb 2011 13:55:36 +
Tompkins Neil wrote:
> SELECT DISTINCT(away_teams_id) AS teams_id
> FROM fixtures_results
> WHERE (fixtures_results.away_users_id = *users.users_id*)
> Any ideas why I'm getting Unknown column 'users.users_id' in 'where clause&
Hi,
I've the following SELECT statement
SELECT users.gamer_tag, UNIX_TIMESTAMP(users.created_on) AS time_registered,
(SELECT fixtures_results.last_changed
FROM fixtures_results
WHERE (home_users_id = users.users_id AND home_teams_id =
users_teams.teams_id)
OR (away_users_id = users.users_i
On 01/31/2011 12:18 PM, Jørn Dahl-Stamnes wrote:
> On Monday 31 January 2011 21:12, Phillip Baker wrote:
>
>> Greetings All,
>>
>> I am looking for a little help in setting a where clause.
>> I have a dateAdded field that is a DATETIME field.
>> I am look
On 1/31/2011 15:12, Phillip Baker wrote:
Greetings All,
I am looking for a little help in setting a where clause.
I have a dateAdded field that is a DATETIME field.
I am looking to pull records from Midnight to midnight the previous day.
I thought just passing the date (without time) would get
Thank you very much Jørn
Blessed Be
Phillip
"Never ascribe to malice what can be explained by incompetence"
-- Hanlon's Razor
On Mon, Jan 31, 2011 at 1:18 PM, Jørn Dahl-Stamnes
wrote:
> Jørn
On Monday 31 January 2011 21:12, Phillip Baker wrote:
> Greetings All,
>
> I am looking for a little help in setting a where clause.
> I have a dateAdded field that is a DATETIME field.
> I am looking to pull records from Midnight to midnight the previous day.
> I thought jus
1 - 100 of 1556 matches
Mail list logo
