Hi,
you're using Sqlserver syntax for handling reserved words.
In MySQL you use backtick` for the same, i.e.
select `t0`.`amount` etc.
/Johan
Sharique uddin Ahmed Farooqui skrev:
Hi,
I'm using VS Express 2008, and trying to use linq with Mysql.What I have done
1. Created a db with a s
Hi Hiep,
Hiep Nguyen skrev:
On Thu, 3 Apr 2008, Johan Höök wrote:
Hi Hiep,
you can put in either xxx = NULL
or you can skip it completely:
insert into tbl_1(fld2,fld3) select fld_b, NOW() from tbl_2;
Regards,
/Johan
Hiep Nguyen skrev:
hi all, i have a question on insert ... select
Hi Hiep,
you can put in either xxx = NULL
or you can skip it completely:
insert into tbl_1(fld2,fld3) select fld_b, NOW() from tbl_2;
Regards,
/Johan
Hiep Nguyen skrev:
hi all, i have a question on insert ... select statement.
tbl_1(fld1,fld2,fld3, )
fld1 int primary key auto_inc
Hi,
what you probably want is
SELECT id, start_date FROM iddt WHERE id IN
('109k7','s3x6','sxmns','wt57')
ORDER BY FIELD(id,'109k7','s3x6','sxmns','wt57')
/Johan
Papalagi Pakeha skrev:
On 10/30/07, Sebastian Mendel <[EMAIL PROTECTED]> wrote:
Papalagi Pakeha schrieb:
Hello,
I have a query lik
Hi Frederico,
the precedence between the comma-operator and JOIN changed
with 5.0.12.
See http://dev.mysql.com/doc/refman/5.0/en/join.html
Excerpt from that article:
Previously, the comma operator (,) and JOIN both had the same
precedence, so the join expression t1, t2 JOIN t3 was interpreted as
Hi,
what you want to look into is (depending on your version)
either "INSERT ... ON DUPLICATE KEY UPDATE ..." or REPLACE
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
http://dev.mysql.com/doc/refman/5.0/en/replace.html
/Johan
[EMAIL PROTECTED] skrev:
Hi,
I want to update a ta
Hi Jonathon,
MySQL is using the index for both queries,
as the column "key" in the explain result says.
Using index means that MySQL can find all info
it needs by looking at the index only, i.e doesn't
need to look into the table.
See:
http://dev.mysql.com/doc/refman/5.0/en/explain.html
for more
Hello,
you can do it in two ways I guess:
one is to do a second select (which you don't want):
SELECT LAST_INSERT_ID().
another way is to use java.sql.Statement.RETURN_GENERATED_KEYS
when you create your Statement, something along these lines:
java.sql.PreparedStatement pstmt =
myconnec
Hi,
I'm including your post to the forum as well.
The problem I think is the fact that you need to
do LEFT JOIN in two directions which quite often
don't turn out what you want.
So what you can do is to do two queries, UNION
them together and form a derived table that you
then do your final SELECT
Hi Kevin,
I didn't look that close at it but it
should be IFNULL, not ISNULL which is SQLserver's
version of it.
/Johan
Zembower, Kevin skrev:
I'm trying to use a subquery for the first time, and am having some
problems. I'm hoping someone can give me some suggestions on ways of
doing what I wa
Hi,
what I see is that you at least will get everything
where Approveddate IS NULL. You will have to add on
parentheses around (Approveddate between '2006-09-14' and '2006-09-21'
or Approveddate Is Null) as AND has precedence over OR.
Also if your query always will be against "like '%'" you shoul
Hi Jerry,
I don't think that will work as I think
you'll only get hits in tablea on what exists in
tableb, i.e. you wont get 1/a as your JOIN will exclude
that as it will only join for non null existencies.
Your query will return 2/b and 3/c as they exist in tableb
and have flag != 'Y' and miss 1/
.a_id b.b_id
1 NULL
2 1
3 NULL
you then apply the where and end up with 1 and 3.
As long as you every restriction in the JOIN clause
and only the IS NULL in the where clause it should work.
/Johan
mos skrev:
At 03:16 AM 9/20/2006, Johan Höök wrote:
Hi André,
you can do it like:
Hi André,
you can do it like:
SELECT a.*
FROM tablea a
LEFT JOIN tableb b ON b.a_id = a.a_id AND b.flag = 'y'
WHERE b.b_id IS NULL;
/Johan
André Hänsel skrev:
Hello list,
I have two tables:
Table A
a_id name
1a
2b
3c
Table B
b_id a_id flag name
12yx
22ny
3
Hi Jörn,
I don't think you can do it in one SELECT
as you'll get the same number (the max)
as soon as the COUNT goes above zero.
If you think about how your resultset looks
if you remove your COUNTs it becomes clearer.
Say that for one a.a you have 3 matches in b
and 2 matches in c, this will resu
Hi Peter,
I'd thought I'd just mention that the varchar length
depends on your MySQL version and character set.
5.0.3 and later handles upto 64k chars.
See: http://dev.mysql.com/doc/refman/5.0/en/char.html
/Johan
Peter Van Dijck skrev:
Hi,
URL's have a practical limit of 2083 characters it seem
Hej Jörn,
the problem lies in that you added your
b.verified=1 condition in the WHERE part,
what happens then is that you more or less transform
your left join into an inner join as you add on a condtion
for existing rows. If you do your query as:
SELECT a.name,COUNT(b.id) FROM x_type AS a LEFT JO
Hi Ravi,
you can take a look at:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
which might take care of your problem.
/Johan
Douglas Sims skrev:
Hi Ravi
You are correct. I was just sitting here thinking about this after I
sent that and realized that what I said was incorrec
Hi Neil,
what you need is a LEFT JOIN:
SELECT a.*
FROM TableA a
LEFT JOIN TableB b ON a.ID = b.ID (assuming ID is what you relate the
tables on )
WHERE b.ID IS NULL;
should hopefully do what you want.
/Johan
Neil Tompkins skrev:
Hi,
I've two tables. What q
--- Begin Message ---
Hi,
what you want is probably described in:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
/Johan
The Nice Spider skrev:
not works. it's still show same result. any idea?
- Original Message -
From: "Quentin Bennett"
<[EMAIL PROTECT
Hi Chris,
what you can do is:
SELECT [fields]
FROM [table]
WHERE id IN (id1,id2,id3...)
ORDER BY FIELD([field],value1,value2,value3,...)
/Johan
Chris Sansom skrev:
Yes, I have looked at the docs and can't find what I'm looking for.
I'm doing a very simple query:
SELECT [fields]
FROM [table]
Hi,
yes it makes a huge difference,
as the tables are stored in files and therefore if
the filesystem is case-insensitive (Windows) you can
use either case in your queries, while on a case-sensitive
filesystem (linux etc) you can't.
See: http://dev.mysql.com/doc/refman/5.0/en/cannot-find-table.htm
Hi,
I guess you should be able to do something like:
SELECT t1.term_id, t1.name, r.type_id, t2.term_id, t2.name
FROM term t1
LEFT JOIN relationTerm r ON r.term_id1 = t1.term_id
LEFT JOIN term t2 ON r.term_id2 = t2.term_id
/Johan
mel list_php skrev:
Hi!
I'm stuck with a join query
2 tables
Hi Barry,
see: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
you cannot insert into a table you're doing select on
(same goes for update).
Regards,
/Johan
Barry skrev:
I get this error:
Fehler in
/home/virtual/site4/fst/var/www/html/adminheaven/artikel-vererben-save.php
in
Hi,
I think your problem is that OR and AND do not
have the same precedence, AND binds tighter.
So what you need is probably:
(products_model="5217-01" OR products_model="5217-01") AND
products_um="CS" and the same for "PK" and "EA".
The way you have you'll get an update as soon as
products_mode
Hi Ian,
if you look closer at the picture of the second you'll see
that it's the German version of the book.
/Johan
Ian wrote:
Hi,
On Amazon uk there are two versions of the book "High Performance MySQL ":
1st:
High Performance MySQL
~Jeremy D. Zawodny, Derek Balling
O'Reilly
Paperback - Ap
format, you might run into problems
with that I guess. Also you might have to play with lengths a bit.
/Johan
Gobi wrote:
Johan Höök wrote:
Hi Gobi,
there was a similar posting in august.
See:
http://lists.mysql.com/mysql/187436
which I think describes what you want.
I'll include a bit of
Hi Gobi,
there was a similar posting in august.
See:
http://lists.mysql.com/mysql/187436
which I think describes what you want.
I'll include a bit of it here as well
--
This is out of the MySQL class and is called the Max-Concat trick.
_
Hi Priscilla,
you can do:
SELECT (SELECT SUM(t1.field) FROM table1 t1) +
(SELECT SUM(t2.field2) FROM table t2) +
(SELECT SUM(t3.field3) FROM table t3)
/Johan
Priscilla Labanca wrote:
Hi, guys!
I need to sum three fields of three different tables how can I do?
Thank you ve
ry_id,category_id,category_name
when MySQL deduces what to use for columnnames in the view.
/Johan
Terence wrote:
Johan Höök wrote:
Hi Terence,
I think your problem lies in your SELECT *
If you look at the columnheaders below you get category_id twice.
I guess you have to specify your columns with al
Hi Terence,
I think your problem lies in your SELECT *
If you look at the columnheaders below you get category_id twice.
I guess you have to specify your columns with aliases.
/Johan
SELECT * FROM ticket_master tm, category_master cm
WHERE tm.category_id = cm.category_id;
+---+
Hi Luis,
you can start your server with --skip-grant-tables
see:
http://dev.mysql.com/doc/mysql/en/privileges-options.html
/Johan
Luis Garay wrote:
hi
im pretty newbie in mysql. i installed this in my computer few weeks ago and
today i want to begin practicing and i cant log in . i suppose i f
Dave,
I've tried
SET collation_connection = utf8_general_ci;
on my server (4.1.14) and it seems to work so
I'm afraid I can't help you anymore there.
/Johan
Dave wrote:
My hunch here is that your ' ' in the CONCAT_WS defaults to
latin1_swedish_ci.
It seems you're right. There are a few var
Dave,
I'm not that good when it comes to collations but latin1_swedish_ci is
normally the default collation for a MySQL installation. Have you
checked via "SHOW VARIABLES" that you don't have latin1_swedish_ci
lurking somewhere. My hunch here is that your ' ' in the CONCAT_WS
defaults to latin1_sw
Hi Dave,
I think your problem is that you're trying to call one of
your columns MD5(passwd) and my guess would be that you should do:
INSERT INTO forum_members( passwd, dateRegistered, realName,
ID_MEMBER, memberName, emailAddress, active, keitai, number, admin,
cardpic, cardbio, hofpic, hofbio
Hi Hal,
in order to get INSERT IGNORE to work as you want it you must violate
a unique index somehow, i.e. you must have a unique index on Name,Value
or both and then you would get a quiet ignore of that violation.
The IGNORE keyword doesn't make the INSERT as such different, it just
affects the e
Hi,
the basic thing is that you must never assume anything on what
order you're getting your rows back if you're not using an order by.
This said I guess one way for you to do this is to add a row-number
column, preferbly auto-increment, and then order by that column.
/Johan
Schimmel LCpl Rober
Hi Jocelyn,
you're right of course. I didn't read the original post
closely enough (it's early in the morning here...).
I missed that SELECT wasn't used.
Regards,
/Johan
[EMAIL PROTECTED] wrote:
Hi Johan,
I don't think it's its problem, since MySQL returns a "You have an error
in your
Hi Siegfried,
I think you've run into bug #8732:
...
Description:
if you do a INSERT INTO table (col_list) SELECT ... ON DUPLICATE KEY
UPDATE and
refer to the same column in the col_list and in the UPDATE clause, it
will bail
out, doesn't seem like it should, since not listing the column in the
Hi Scott,
I think you've been lucky so far...
As you're only ordering on listdate, which is the same
for both homes in your example you might definitely get different
results once you put in the limit.
A basic thing about rdb's is that you must never make assumptions
that it returns resultsets in
Hi Shaun,
I guess you could try something like:
UPDATE table SET col = RIGHT(col, LENGTH(col) - 3 ) WHERE ...
/Johan
shaun thornburgh wrote:
Hi,
is it possible to alter fields in a column by removing the first three
characters?
i.e. change 100123456789 to 123456789
Thanks for your help
--
MySQ
Hi Ezequiel,
see:
http://dev.mysql.com/doc/
/Johan
Ezequiel Rodriguez wrote:
well im from argentina, and today i begin using mysql, i have a lot of
questions, first of all, is there a mysql official manual at www.mysql.com? i
have searched but don't found it :S
I want to read something before ask
Hi Stano,
there was a response by Michael Stassen on the list about a year ago on
this problem, which I hope he doesn't mind if I repeat below.
/Johan
Version numbers?
CREATE TABLE ss (version VARCHAR(13));
INSERT INTO ss VALUES ('1'),('1.1'),('1.2.1.2'),('1.10.1'),('1.2'),
Hi Scott,
you can do:
SELECT a, b, c, from table where zip IN (94949, 94945, 94947)
ORDER BY FIELD(zip,94949, 94945, 94947)
/Johan
Scott Haneda wrote:
mysql 4.0.18-standard
I am running this select:
SELECT a, b, c, from table where zip IN (94949, 94945, 94947)
How can I get back a result set in the
Hi,
according to the manual:
http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html
150 is "a foreign key definition would be incorrectly formed for the
altered table"
/Johan
Joppe A wrote:
Hello all,
I have a problem with a script I have made, it always stops when it tries to creat
Hi Martijn,
yes of course you're right but
SELECT COUNT(s.Id)+
(SELECT COUNT(se.Id) FROM subs_erased se WHERE s.Id=se.Id)
FROM subs s
WHERE s.Id=1
might work, at least in 4.1.x. I did test it with 4.1.9.
/Johan
Martijn Tonies wrote:
Hi,
why not try:
SELECT COUNT(s.Id)+COUNT(se.Id)
FROM subs
Hi,
why not try:
SELECT COUNT(s.Id)+COUNT(se.Id)
FROM subs s
INNER JOIN subs_erased se ON s.Id=se.Id
WHERE s.Id=1;
/Johan
Joppe A wrote:
Hello,
I have a small problem that is probably easy to fix but it is to advanced for me as a
"newbe". In my sql-query (below) have I the ID specified twice, I wo
Hi,
You can probably use "SELECT LAST_INSERT_ID()" which keeps
auto-increment values on a per connection basis.
See:
http://dev.mysql.com/doc/mysql/en/getting-unique-id.html
/Johan
mel list_php wrote:
Hi!
I have a database where several users can connect and input data.
I managed to have my insert
Hi,
could you check to make absolutely sure that your
taskId column isn't tinyint, which should explain
it as it's max ( being signed ) is 127.
/Johan
mel list_php wrote:
Hi list,
I have a very strange (and worrying..!!!) problem with my tables.
I'm running a 4.0.9 gamma (no choice for that) adn I
Hi,
I guess your "CASE" statement should look something like:
CASE WHEN Location=1 THEN 'Downstairs Cat Room'
WHEN Location=2 THEN 'Kitten Room'
WHEN Location=3 THEN 'Quarantine'
ELSE 'Unknown' END AS Location
/Johan
Sue Cram wrote:
Thanks to the people who helped me with my IF state
Hi,
I guess you juat should add on a " AND fh1109.state NOT IN
('TX','PA','ME')"
Also I think your AND conditions are a bit overdetermining, you can
probably do just :
"WHERE fh1109.state = ssa1202.state and
fh1109.cd = ssa1202.cd and
fh1109.state = vapall.state and
fh1109.cd = vapall.cd and
A
y, is there a way to make mySQL treat "NULL" as "0"
when dealing with mathematical functions?
Tim Gustafson
MEI Technology Consulting, Inc
[EMAIL PROTECTED]
(516) 379-0001 Office
(516) 480-1870 Mobile/Emergencies
(516) 908-4185 Fax
http://www.meitech.com/
--
Johan Höök, Pythagor
| MUL | 0 |
|
++---+--+-+-+---
-+
How would I select all distinct member_id that have a specialty_id of 6 and
33?
Thanks!
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: <http://MikeZornek.com>
--
Johan Höök, Pythagoras Engineering Group
- MailTo:
53 matches
Mail list logo