The solution was CREATE TABLE copyname SELECT * FROM originalname
I was having problem with a PHP command that's not pulling what I want
from the table Ruling out random order for the rows was narrowing the
focus on the PHP problem.
Why not just create a VIEW and leave your original table
I want to find the value 123 in the field id in any one of these
tables.
SELECT id FROM TableA WHERE id = 123
UNION
SELECT id FROM TableB WHERE id = 123
UNION
SELECT id FROM TableC WHERE id = 123
Please note that UNION requires that the same number (and type?) of
fields be selected from each
I want to delete from the 'Item' table
all the items identified by the folowing query:
If you have MySQL 5+, you can do it using a sub-query:
DELETE FROM
Item
WHERE
ProductID IN (
SELECT
Item.ProductID
FROM
Item, ItemTag
WHERE
ItemTag.TagID = '168'
AND
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without rounding off. we
just want the '54' value. We can do it easy enough with our Perl
programming, but was wondering if
Okay, so I have been gooling all over trying to
figure this out. I'm sure it's easy enough to do,
but I can't seem to find it.
All I want to do is figure out the difference between
2 fields. IE:
Field 1= 20
Field 2 =10
Difference between Field 1 2 is: 10
Any ideas?
Umm, basic math?
My UK dates are this format DD/MM/ I want
it reversed and then the seperator changed so it becomes
-MM-DD
I use this PHP at the moment
$available_from = implode('/', array_reverse(explode('-',
$available_from)));
An even better solution would be:
$UKDate = '22/05/2007'
$USDate = date(
Take a look at the following:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#func
tion_unix-timestamp
That looks like exactly what you need...
thnx,
Chris
-Original Message-
From: Bryan Cantwell [mailto:[EMAIL PROTECTED]
Sent: Monday, May 21, 2007 4:45 PM
To:
Do you mean that I should replace SELECT * FROM
with SELECT field1, [field2],[...] FROM?
Yes.
If so, I need to specify the table name like SELECT
Clients.Name correct?
You only need to do that when 2 tables have the same column name. In
general, though, it's good practice to always
I tried this out this morning on MySQL 5.
It works. Please try this in MySQL 4 and see.
Unless I'm way off, I do not believe your solution will work in 4.x because
it doesn't support sub-queries...
thnx,
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
Here is a query I'm trying to run. It doesn't look like it is overly
complex and, granted, it's joining on a few tables, I wouldn't imagine it
would take 6.5s to execute.
SELECT
TRIM( users.username ) AS username,
TRIM( games.game_name ) AS game_name,
CASE
WHEN 0 != UNIX_TIMESTAMP( MAX(
Make sure that all joined fields are of identical types, otherwise the
query executor must cast each and every join value, which may be
affecting the query time...
Can you even define fields having foreign keys to be of a different type?
Anyway, taking a look at my JOIN
INNER JOIN users ON
GROUP_CONCAT() is in 4.1. :-)
Oh, so close yet so far. We're running 4.0.14 and my company has
no plans on upgrading in the near future. *sigh*
Thanks for the info, however!!
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
I have 2 tables (looks best in fixed width font):
test_items
+---+--+
| name | rec_num |
+---+--+
| Book | 1|
| Game | 2|
+---+--+
test_attributes
+---++
| item_rec_num | attribute |
+---++
I want a list of table1.id not included in Table2 but i don't find the
solution!
I _*think*_ this will work for you. If I'm wrong, I'm sure someone
will come behind and correct me:
select table1.id from table1 left outer join table2 on table1.id = table2.id
where table2.id = NULL;
Chris
select table1.id from table1 left outer join table2 on table1.id =
table2.id
where table2.id = NULL;
Sorry, that last should be
where table2.id IS NULL;
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL
If you are not using 5.0 and don't want to switch to 5.0, then using
database view will also work. This link tells how to create views:
http://www.mysql.com/documentation/maxdb/6d/117c44d14811d2a97400a0c9449261/content.htm
What version does this work for? We're using 4.0.14 and it doesn't
How do I reset an AUTO_INCREMENT column? My table type is InnoDB and
the method mentioned in the manual is not applicable. I am using MySQL
4.0.17.
Otherwise you should recreate the table.
Or, if you no longer need any of the data, simply use TRUNCATE.
Chris
--
MySQL General Mailing
Can you UNION 2 queries and use the result to JOIN with another table?
(SELECT tableA.name, tableC.company FROM tableA)
UNION
(SELECT tableB.name FROM tableB)
LEFT JOIN tableC ON tableA.name = tableC.name;
This doesn't work. But I don't know if it isn't working because I have the
wrong syntax
It used to be that I used SQL for basic stuff and did the rest in
my code. Now I'm starting to learn some of the cooler features
of SQL and I'm trying to figure out what I can move into a query
and what I'll still need my code to do thing. In trying this conversion,
I've run across something
I'm working with data that has not been normalized. If it were
up to me and I had the time, I'd go in and change all the code
so that the data were normalized, but right now that's not an
option.
The data I'm working with looks like this:
table1.columnA = '1;3;4;6;8;9;12;13;14;15';
SELECT vend_city+', '+vend_state+' '+vend_zip
FROM Vendors
ORDER BY vend_name;
SELECT
CONCAT( vend_city, ', ', vend_state, ' ', vend_zip )
FROM
Vendors
ORDER BY
vend_name
The solution is to concatenate the three columns. In SQL SELECT
statements, you can concatenate columns using a
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
--
MySQL
Access. However, when I run it against MySQL I get an error.
select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c
where l.custsysid=c.custsysid
and l.ltsysid in (select l2.ltsysid from lientrak as l2 where
l2.lientraknum
like '2003-%')
Sub queries are not going to be
Or from my place, It pays for me to read the docs - thoroughly. Oh!
Docs! What are they? :-[
Or just mysqldump --help :p
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
When I type a command after the mysql prompt nothing happens and no
error. For example:
mysql CREATE DATABASE members
SHOW DATABASES
What do you think is the problem.
This is an apache web server.
The OS is Redhat
This is just a shot in the dark, but there are no semi-colons at
Hello. I'm looking for a GUI client that will run under Windows 2000
and attach to Solaris 8 server that will allow me to see all the tables
and data in my mysql database.
Others have mentioned phpMyAdmin. While that is an excellent piece
of code, I find that it's someone slow to do what I
On an entry not all fields of the four quarter fields are covered with
values. Following combinations are possible:
Why don't you just use one extra column (apart from whatever other
data you are storing) with that column being a date field. Then, you
can determine what data is part of
What is wrong with this table creation
CREATE TABLE `spid_1__0` (
`recordname` varchar(255) NOT NULL default '',
`data` enum NOT NULL,
PRIMARY KEY (`recordname`,`data`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
What is the specific error you are getting?
W/o knowing that information, I
But that's not what a ROWID is compared to what I think the original poster
was looking for. In Oracle for example, a ROWID is the unique address of a row
in the database. Every row, unique key or not has a unique address. Is there
such a thing in MySQL? ROWIDs are extremely useful for
I'm trying to do something like the following:
SELECT
CASE status
WHEN
IN ( 'BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
WHEN
IN ( 'NA', 'NG', 'RA' )
THEN
'approving'
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));
But this
SELECT
CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
CASE WHEN status IN('NA', 'NG', 'RA')
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));
Sadly, that didn't work:
Your MySQL connection id is 208517 to
I didn't have your table so I tried to write the same command by one of my
tables. my query which worked, is:
SELECT CASE WHEN left( workorder, 1 )
IN ( 'C', 'T' )
THEN 'group1'
WHEN left( workorder, 1 )
IN ( '3', 'R' )
THEN 'gorup2'
ELSE
'group3'
END FROM workfile;
which is
Is there a way to select/query the table structure and get a recordset
with the results?
Something like:
Id, int, not null, autoincrement
name, varchar(20), not null
DESC table_name
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Hello, my name's Marlon. I have a question about sql and I need some help!
How can I do something like it using mysql?
update registre set (name='NewName' where lastname='OldLastName'),
(name='OldName' where lastname='NewLastName');
I _believe_ you can do it this way. I'm sure someone
Is it possible to Join 2 tables in 2 different databases? Can someone
give a quick example?
select database1.table1.* from
database1.table1, database2.table2 where
database1.table1.common_field = database2.table2.common_field.
Chris
-mysql,query,blah
--
MySQL General Mailing List
For list
sub-queries, but also seen examples of it done. Is it only supported in a
later version? We are running v. 3.23.3.
As far as I know, subqueries are only supported in MySQL v4(.1?)+
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
I am trying to select the column names from a table to be displayed in a
web page. Is there anyway to select column names without desc?
If you are using PHP, you can look at the following:
http://us4.php.net/manual/en/function.mysql-field-name.php
Chris
--
MySQL General Mailing List
For
How can I see if a record was deleted from a database?
You can attempt to SELECT it, and if you get no result, it's not there.
But that doesn't necesarily mean that it was once there and has now been
deleted. If you want to determine that, you'll need to create a log
of record deletions.
Easiest way to do what you want is to make this:
1.SHOW CREATE TABLE TableName
and copy SQL
2. DROP TABLE TableName
3. CREATE TABLE - with SQL copied in 1.
Actually, the easiest way to do this (assuming *all*
records have been deleted) is:
UPDATE table_name SET auto_increment_field = 0;
truncate table_name
does both in one statement. And even optimizes the table (frees up
unused disk space). However take care that you cannot rollback this DDL.
Wow. Learn something new every day! :p
Thanks for the tip, Rudy!
Chris
--
MySQL General Mailing List
For list archives:
I'm beating my head against the wall. I just can't see
what's wrong with it; what would be causing the error:
SELECT certificate.cert_num, master_info.uid
FROM certificate
JOIN master_info ON ( certificate.uid = master_info.uid )
LEFT JOIN endorsements ON ( certificate.cert_num =
Why isn't the key being used in the c (certificate) table?
SELECT DISTINCT a.uid, a.company, a.firstname, a.lastname
FROM master_info a, logins lsl, logins lc, certificate c WHERE
a.uid = lsl.uid AND lc.parent = lsl.uid AND lc.uid = c.uid AND
c.void 1 AND c.status IN
('CP', 'MC',
Why isn't the key being used in the c (certificate) table?
SELECT DISTINCT a.uid, a.company, a.firstname, a.lastname
FROM master_info a, logins lsl, logins lc, certificate c WHERE
a.uid = lsl.uid AND lc.parent = lsl.uid AND lc.uid = c.uid AND
c.void 1 AND c.status IN
('CP', 'MC', 'AIC',
We are running v4.0.12.
Consider the following:
mysql SELECT MAX(assets) as assets
- FROM do_deductibles
- WHERE currency = 'usd';
++
| assets |
++
| NULL |
++
1 row in set (0.00 sec)
mysql SELECT MAX(assets) as assets
- FROM do_deductibles
- WHERE
Use table type =Innodb for foreing keys and transacionts...etc
and type =myisam for speed
Yes, but do the foreign keys in InnoDB honor the constraints? I
think that was the original question. What's the poing of using
foreign keys if it's not going to maintain the integrity of your data.
That
Why can't you use an Env variable to point to the server you want to query
from and switch the value back and forth as needed in the case that you want
to periodically switch back and forth between servers? Or write a function
that runs on server1 to query against the tables on server2 making
We have a *nix network and on that network we have 2
web servers. One for development (server1) and one for
production (server2). And on each of those servers we
have MySQL installed. Is there a way we can query a
table on server2 from server1?
I know that when you are using a database from
You can use the command line mysql with the host option, i.e.
--host=hostname, to connect to another machine. Likewise, you could set
up a data source using ODBC to look at the database on the second host
machine through which you could generate queries against the tables on the
other host's
Why is it that I can not use two mysql queries in the same PHP file.
You can, I assure you.
Any info is appreciated.
Perhaps if you could provide your code? W/o it there is no way we can
determine where the problem might be coming from.
Chris
This is the query that was given to me:
SELECT e.*, c.* FROM
correspondence e, certificate c, logins l WHERE
c.void1 AND
c.uid = l.uid AND
l.parent = wd001 AND
e.invoice_num = c.invoice_num AND
e.to_uid = wd001 AND
e.actioned= 0
ORDER BY e.message_num
Pretty
Yesterday, someone (I cannot recall who) posted a link
for a piece of software called My Database Manager.
I tried going to that link on and off for the last day and
I'm always getting a 404. Has anyone successfully DL'd
this app? If so, could you send me a copy? I'd be very
interested in
I see the above referenced throughout the documentation
in the context of foreign keys. What are the possible
choices for CONSTRAINT symbol. I've searched the
docs and have come up empty in this regard.
Chris
sql,query
-
stored? Why isn't this done automatically as it is for [ISAM] tables?
As for the why, I'm not a MySQL developer, but I believe the reason
goes something like this: When ISAM tables were implemented, they did
it the wrong way. When other table types came along, they fixed
this bug and do it
sql, query (*sigh*, I hate this filter)
I have an auto_increment key set up on my InnoDB table.
Whenever I delete all the records, the number isn't reset.
However, for my ISAM tables, whenever I delete all the
records, the auto_increment number is reset.
Is there a reason for this in InnoDB? Is
CB However, for my ISAM tables, whenever I delete all the
CB records, the auto_increment number is reset.
CB Is there a reason for this in InnoDB? Is there a way that I
CB can reset the auto_increment number when all the records
CB are deleted?
try exec this query:
alter table TABLE_NAME
Is there any way I can do something like this other than
create a little script to do it programatically?
UPDATE interests_shipping_conditions_master SET
interests_master_record_num = interests_master.record_num WHERE
interests_shipping_conditions_master.interest_name =
Of the data? Or of the queries?
Not the data. Just your query, the output from explain and a create
table statement.
Query:
SELECT DISTINCT cards_crypt.*
FROM user_haves, cards_crypt WHERE
cards_crypt.name LIKE %harrod% AND
user_haves.card_name = cards_crypt.name AND
When dealing with a considerable number of records
(10s of thousands) in a particular table, which is better
to use? Currently we are using MyISAM and the
queries on those tables are kind of slow. I've set the
most frequently used (in a query) columns as keys and
that isn't speeding it up any.
When dealing with a considerable number of records (10s of
thousands) in a particular table, which is better to use? Currently
we are using MyISAM and the queries on those tables are kind of
slow.
Really? That's not a lot of data. Can you provide examples?
Of the data? Or of the queries?
When dealing with a considerable number of records (10s of
thousands) in a particular table, which is better to use? Currently
we are using MyISAM and the queries on those tables are kind of
slow.
Really? That's not a lot of data. Can you provide examples?
Of the data? Or of the queries?
Yes, the concepts he presents are in generic SQL. I've used them in 2
different systems (menus and security) and they work fine. (NOTE: You DO
have to write your own code.) ;)
Ok, I picked up the book yesterday. It has alot of really good information.
However, with regards to the concepts
Is there a way to do this in one query (never mind the possibilities
for endless loops):
Say you have a table that looks like this:
fieldName dependantOn
joe monster
bob joe
briggs
monster briggs
vision
I want to do a query so that the records are
SQL for Smarties by the demigod Joe Celko. Chapters 28 and 29 describe this
tree structure. READ BOTH OF THEM, even though you won't use the code in
28. Chap 29 holds the simplified concepts to make a tree structure work with
single select.
It's not easy. It's a complex concept but once
I downloaded the binaries yesterday and installed on my WinXP
laptop. I read the documentation that came with it but couldn't
seem to get it to run. It seemed like any executable I ran, it would
open up a DOS window which would then close very quickly. I
tried everything to get it to work
I've looked through the documentation and if this question
is answered there, please point me to the relevant section
because I've obviously missed it.
What foreign languages does MySQL support definitively.
As in can do all kinds of searches in, etc? I'm curious about
Chinese and Japanese (and
How do I find out a next and preview row values by using PHP and MYSQL. For
examples,
Take a look at the function
mysql_result();
Chris
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
The solution I have been using is to do three queries similar to the below
SELECT * FROM table WHERE field='ID00025'
SELECT * FROM table WHERE field'ID00025' ORDER BY field DESC LIMIT 0,1
SELECT * FROM table WHERE field'ID00025' ORDER BY field ASC LIMIT 0,1
If you whish more row returned
It seems worst to me because in your case mysql has to retrieve all the
rows. If it's a table with 1 million records or more, this should hurt ;)
As I said, it was pseudo code. Now, imagine that you were just
getting the records for a particular user? a particular application?
Where there
I couldn't find anything like this in my MySQL book or in the on line help.
Could this be what you are looking for?
http://www.mysql.com/doc/E/X/EXPLAIN.html
Not sure exactly what you need to know...
Chris
-
Before
Given this example:
table CARS
has got an entity Labourghini with car_id=5
table NEWS
has got an entity Lambourghini displays new super model with
date=2002-05-02, news_id=735 and car_id=5
I want this output:
NEWS HEADLINES
date: headline (car_brand)
Can I do this without 2 select
I've looked through the docs and I couldn't find out how
to do this. If you can point me to the right place in the
documentation so I can read how, that would be great. If
you could give me a little sample query to start from, that
would be great as well.
I've 2 columns in a table. Status and
Is there a way to do this? If so, I've not been able to
find it in the documentation...
I have 3 fields:
PrimaryKeyField
StatusField
DueField
I want to update the DueField to 0 (zero) where the
PrimaryKeyField = XYZXYZ. I also want to update
the StatusField for those records to be equal to C
72 matches
Mail list logo
