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 find For
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.last_name, c.last_n
LESCE(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:
WHERE COALESCE(f_tag_bottom, f_tag
IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
>>>>>>>>STDDEV(ch_x_top)
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
u don't need the last 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 i
ALESCE(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:
WHERE COALESCE(f_tag_bottom, f_tag_bottom_majo
DEV(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, it is equivalent to this:
nd you don't need the last 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
; Of course, this isn't your real problem, but you could use COALESCE
>>>>> instead
>>>>> of all those IFNULLs (and you don't need the last one):
>>>>> SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
>>>>> f_tag_bottom_mino
(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, it is equivalent
; 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_bottom_minor_axis AS ftag FROM da
On Mon, Jan 25, 2016 at 8:26 PM, Johnny Withers <joh...@pixelated.net> 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_
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_bottom_minor_axis AS ftag
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
chrieb 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:
>>>
>>> SELEC
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
On Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald <h.rei...@thelounge.net> 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.
>>
>>
On Mon, Jan 25, 2016 at 8:01 PM, Rebecca Love <wacce...@gmail.com> 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
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
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/
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
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/mysql/20140323
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/20140323/EXPLAIN.txt
I don't really know what is tells
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 .
Regards,
Christophe.
--
MySQL General Mailing List
For list
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 was is there any performance impact
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);
Indeed ! it can be a significant performance improvement ;).
Thanks for this .
Actually
From: Christophe t...@stuxnet.org
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 case / from lenny and squeeze Debian
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 processed first ?
I'd like the first filter
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 not matter
that is simply
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*. This is a
different question
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/multiple-column-indexes.html
Yes, the order
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', 'ACCEPTED') AND SubscribeDate DATE_SUB(NOW(), INTERVAL
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', 'ACCEPTED
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', 'ACCEPTED
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, Christophe t...@stuxnet.org
Awesome! Thank you very much Claudio! :)
On Thu, Nov 21, 2013 at 10:40 AM, Claudio Nanni claudio.na...@gmail.comwrote:
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 BY username
HAVING
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 like a WHERE total_usage 322100
-
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...;
Possibly run your constructed query thru
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
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
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 of records?
how
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 is possible to block
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
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 trick.
Arthur
Possibly run your constructed query thru a regex expression e.g.
String mydata = SELECT * from table WHERE ab;;
Pattern pattern = Pattern.compile('WHERE');
Matcher matcher = pattern.matcher(mydata);
if (matcher.find())
{
//WHERE clause found proceed normally
}
else throw new Exception(WHERE
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 whose group by count is above 5
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 `GROUP BY'.
your SQL means like
- Original Message -
From: Daevid Vincent dae...@daevid.com
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
On Thu, Feb 23, 2012 at 5:50 PM, Daevid Vincent dae...@daevid.com 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
, `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' AND sd.`scene_id`=s.`scene_id` GROUP BY
sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30
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
On Wed, Oct 19, 2011 at 12:29 PM, Michael Dykman mdyk...@gmail.com 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
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 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
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
On Sat, Sep 10, 2011 at 01:48, Carsten Pedersen cars...@bitbybit.dk 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
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.date` NOW();
ERROR 1064 (42000): You
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`=Jimmy Page
- WHERE
`
-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' in 'field list'
mysql
mysql SELECT count(someField) FROM userTable
: 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: ref
possible_keys: dvd_id_2,dvd_id
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 indexes here with the idea
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't seem
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 have to keep
/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 help
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
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 place.
I want the database folder to get created here
(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 place.
I want the database folder
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
in it,
so it needs to be in a specific place
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 special function
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` WHERE
`Blogs`.`UserId`=`Users
I would go with join rather than where condition.
2011/4/26 Halász Sándor h...@tbbs.net
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
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
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.`Name` IS NULL,
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 Mailing List
For list archives: http://lists.mysql.com/mysql
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
'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 an...@oire.org wrote:
Hello everyone,
Sorry for my beginner question. Actually I have been using MySql for a
long time but I just start using
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
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 an...@oire.org wrote:
Hello everyone,
Sorry
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`=`Users`.`Id`;
1
). 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 - this view for example behaves as expected:
CREATE
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 - this view for example
|
++--+--+--+--+
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 Message
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(*) |
+--+
|1 |
+--+
1 row
(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(*) FROM `myview2` WHERE `TypeName` LIKE
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 COUNT(*) FROM `myview` WHERE `TypeName
-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 getting started working with
VIEWs and stored
`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 `TypeName
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 COUNT(*) FROM `myview` WHERE
` 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 `TypeName` LIKE '%';
(I tried
| TypeName IS NOT NULL |
++--+--+--+--+
| 1 | NULL | |0 |1 |
++--+--+--+--+
1 row in set (0.00 sec)
But when referenced in the where clause in any
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
For
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'
for the part of the statement that I've marked as *bold* and how I can
over
come this problem ?
You have to add the users table to the subquery
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_id
On Thu, 3 Feb 2011 13:55:36 +
Tompkins Neil neil.tompk...@googlemail.com 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
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 it but I keep
getting an empty record set
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 just passing the date (without
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
sq...@dahl-stamnes.netwrote:
Jørn
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
1 - 100 of 1495 matches
Mail list logo
