Take a look at
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.ht
ml
CASCADE: Delete or update the row from the parent table and
automatically delete or update the matching rows in the child table.
Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two
Mark, is the 'secs' column the offset from the minimum value of the
timestamp column?
If so, you might try something like this:
SELECT UNIX_TIMESTAMP(MIN(timestamp)) INTO @min_timestamp FROM my_table;
SELECT uid, timestamp,
UNIX_TIMESTAMP(timestamp) - @min_timestamp AS secs
FROM my_table
ORDER
I spent several minutes looking at your question and your data model and
nothing jumped out at me that precluded you from determining the quantity of
the this vendor's items sold via this data model. I might have missed
something though.
I'm a little concerned that your LineItem table appeared to
I would first try refactoring your SQL to use INNER JOIN statements
instead of the comma separated lists you are currently using. I would also
not use any subqueries. Test this and see if it works for you:
SELECT SUM(li.quantity) as qtysoldytd
FROM LineItem li
INNER JOIN Sales sa
on
m i l e s wrote:
Hi,
I have an odd situation where I was handed just bad data, and while I
have cleaned it up to the best of my ability one hurdle remains.
I have a situation where I have the following example in a field:
Canna ÒBengalÒ.
Note the odd chrs Ò in the field ? I need to
[snip]
Unfortunately the item field has got data with quotes around it. IE
KP-00310. I
need to clean up the data and have the field contain just KP-00310.
Since I
have
10,000 records, I need a update statement or something to clean that up.
Any ideas ?
[/snip]
Darryl,
Unfortunately the item field has got data with quotes around it.
IE
"KP-00310". I need to clean up the data and have the field contain
just KP-00310. Since I have 10,000 records, I need a update
statement or something to clean that up.
To remove all double quotes: UPDATE tablename
On 6/26/05, 2wsxdr5 wrote:
Can someone tell me why this query works...
SELECT UserKey
FROM(
SELECT UserKey, Count(GiftKey) Gifts
FROM Gift
Group BY UserKey
) GC
WHERE GC.Gifts = 3
Why this construction and not simply:
SELECT UserKey
FROM Gift
GROUP BY UserKey
HAVING Count(GiftKey) =
correction in question below, the problem is not in record '7047' but in
the record which starts with the name 'Triad'
also ... I'm using 4.0.20-standard-log
-Original Message-
From: Rob Brooks [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 2:56 PM
To: mysql@lists.mysql.com
Properly, NULL values should be matched with 'foo IS NULL', as opposed
to 'foo = NULL' which, by standard definition, always returns false
regardless of the value of foo
- michael dykman
On Mon, 2005-02-28 at 16:02, Rob Brooks wrote:
correction in question below, the problem is not in record
NULL is an unknown value. Consequently, you cannot compare NULLs the way
you expect. Effectively, = NULL is always false. Instead of
items_online.ID = NULL
you have to use
items_online.ID IS NULL
Michael
Rob Brooks wrote:
correction in question below, the problem is not in record '7047'
In article [EMAIL PROTECTED],
Rob Brooks [EMAIL PROTECTED] writes:
The only difference in the 2 statements is the 'where items_online.ID =
NULL' part.
Clearly in the first set, items_online.ID = NULL in record 7047 ...
Nope. items_online.ID IS NULL for that record, but comparing anything
=
Bob Lockie wrote:
What I really want was
mysql update records set records.prio=2 where records.in=(select
records.id from records, audit_log, audit_log_records where
audit_log.tracker_id=audit_log_records.tracker_id and
records.id=audit_log_records.id and audit_log.operation='D' and
Bob Lockie [EMAIL PROTECTED] wrote:
What I really want was
mysql update records set records.prio=2 where records.in=(select
records.id from records, audit_log, audit_log_records where
audit_log.tracker_id=audit_log_records.tracker_id and
records.id=audit_log_records.id and
I'm still a beginner myself but try something like
SELECT COUNT(YEAR), WINNER1 AS WINNER, WINNER2 AS WINNER, YEAR FROM
CHAMPIONS WHERE CLASS = hd GROUP BY WINNER;
I think this will give you something like:
COUNT(YEAR) WINNER YEAR
2 carl
Carl Schéle, IT, Posten wrote:
Hi!
I got a table, champions, looking like this:
idclass winner_1 winner_2 year
-
0 hd carl mattias 1957
1
Hello, you might want to try select DISTINCT ?
http://www.mysqlfreaks.com/statements/18.php
/Jonas
- Original Message -
From: Carl Schéle, IT, Posten [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, February 17, 2004 3:49 PM
Subject: SQL-HELP
Hello!
I got a table, champions,
Carl Schéle, IT, Posten wrote:
I got a table, champions, looking like this:
id winner_1 winner_2
0carl mattias
1daniel carl
2
* sulewski
[...]
What I need is all records in table 1 that will
not link to table 2 such that relid=rid and vid=46
Sounds like a job for LEFT JOIN...?
Join to the rows you do NOT want with a left join, and put as a condition in
the WHERE clause that a joined column IS NULL. Something like
Roger,
Thank you for the feedback. But unfortunately this doesn't work. The
problem is that rid will never be null. I'm trying to find the item in
tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and
tab2.vid=46 because there is no record in tab2. Not that the record may
Roger,
In regards to my last e-mail what would be great is if I can get all
the records in tab1 then subtract from there all the records that match
the query tab1.id=tab2.rid and tab2.vid=46. The result would give me
what I need but alas mysql doesn't support minus.
Joe
On Friday, January
That is the whole point of a left join.
It joins to a null record when the appropriate right record does not exist.
sulewski wrote:
Roger,
Thank you for the feedback. But unfortunately this doesn't work. The
problem is that rid will never be null. I'm trying to find the item in
tab1 where a
Gerald,
Your right. You and Roger hit it on the head. Stupid me miss read
Roger's original post.
Last night I was banging my head on the left and right joins but I
didn't understand it until I read Gerald's last note. Plus I didn't
realize you can put two conditions in the ON clause which is
Estoy tomando el sol
.
q
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Estoy tomando el sol
.
q
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
---Original Message-
--From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
--Sent: Thursday, July 24, 2003 12:53 PM
--To: [EMAIL PROTECTED]
--Subject: SQL Help...
--
--After some searching around different books/manuals/google I still
can't
--seem to figure out how do to this. What I have
At 02:52 PM 7/24/2003, you wrote:
After some searching around different books/manuals/google I still can't
seem to figure out how do to this. What I have is a table with 4 cols
(task, resource, department, priority) and what I want to do is be able to
select distinct resources and list what their
I looked at the group by option already and I dont think it will do what I
need it to do. I say this because it will only group things in the
priority/task/whatever but that still leaves options for duplicate
resources. Yes, it would get rid of the dup. resources per priority, but
not for the
Try:
select task, resource, department, max(priority)
from table
group by task, resource, department
Regards,
Ulises
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Enviado el: Jueves 24 de Julio de 2003 02:53 PM
Para: [EMAIL PROTECTED]
Asunto: SQL Help...
After
That works great. =D
Knew it shouldn't be that difficult, thanks a bunch.
And it actually works with the Priorities being in text form to (low, med,
hi).
-Nick
At 02:52 PM 7/24/2003, you wrote:
After some searching around different books/manuals/google I still can't
seem to figure out how do to
To: Dathan Vance Pattishall
Cc: [EMAIL PROTECTED]
Subject: RE: SQL Help...
I looked at the group by option already and I dont think it will do what I
need it to do. I say this because it will only group things in the
priority/task/whatever but that still leaves options for duplicate
resources. Yes
PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 4:41 PM
To: Dathan Vance Pattishall
Cc: [EMAIL PROTECTED]
Subject: RE: SQL Help...
I looked at the group by option already and I dont think it will do what I
need it to do. I say this because it will only group things
You want to look at 'group by acctSrv.accountID' rather than a compound
select.
On Mon, 23 Dec 2002, Adam Nowalsky wrote:
Date: Mon, 23 Dec 2002 09:04:32 -0500
From: Adam Nowalsky [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: sql help
hi, wonder if the sql gurus can help with this
Ok this should be easy so I'm prolly going to screw it up, but here goes =D
Your query should be:
SELECT UserID, SUM(points) FROM History WHERE WeekID = 'whatever' GROUP BY
UserIdI think that should do it. Someone yell if its wrong though.
-Nick
I have 2 tables
Users (UserID)
History
* Dave
I have 2 tables
Users (UserID)
History (UserID, WeekID, Points)
When a User record is created a record is inserted into History with the
current WeekID, so for example data could be :
Users
History
--
- 1 - 10
- 1 - 20
exist for WeekID = 2, other for 1 and
2 and so on.
Cheers
Dave
- Original Message -
From: Roger Baklund [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Dave [EMAIL PROTECTED]
Sent: Thursday, May 23, 2002 6:21 PM
Subject: RE: SQL Help Needed
* Dave
I have 2 tables
Users (UserID
* Dave
Thanks for your responses but it's not that much help I need with
my SQL ;-)
None of the 3 suggestions work.
Please look at the examples I gave. I need *all* UserIDs regardless of
whether they have a record in History that matches both UserID and WeekID.
...and that is what I
I might be wrong, but this may get you going in the right direction:
select count(subject) as count, subject from outgoing where auth='USER'
group by subject order by timestamp desc limit 50
This would tell you each subject ever sent by the user, and how many times
that subject has been sent.
of coffee:)
-Original Message-
From: Dan Vande More [mailto:[EMAIL PROTECTED]]
Sent: Sunday, March 17, 2002 1:44 PM
To: [EMAIL PROTECTED]
Subject: RE: sql help examining log table
I might be wrong, but this may get you going in the right direction:
select count(subject) as count, subject from
Hi Viraj,
You can do it using temporary table.
Create temporary table tmp select subject from outgoing where auth='USER'
order by timestamp desc limit 50;
Select count(distinct subject) from tmp group by subject;
drop table tmp;
If the result of the second query is 1 all the last 50 messages
On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
Hi
I am trying to write an auction script that uses mysql as its backend. Each
auction can have upto 25 sub auctions(cells) taking place.
I'm trying to query the DB to give me a list of all the successfull bids
for each cell, for
Hi,
At 11:21 AM 14/02/2002 -0500, you wrote:
On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
Hi
I am trying to write an auction script that uses mysql as its backend. Each
auction can have upto 25 sub auctions(cells) taking place.
I'm trying to query the DB to give me a
At 21:36 +0200 1/30/02, P.Agenbag wrote:
Hi
I need help with an sql string:
have a table similar to this:
idkeynamedate
1 123name1 date1
2 123name1 date2
3 111name2 date1
4 111name2 date2
mysql describe mytable;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| id| int(11) | YES | | NULL| |
| keyq | int(11) | YES | | NULL| |
| name |
Sorry, I meant:
mysql select name,max(dateq) from mytable group by keyq;
-Original Message-
From: Rick Emery
Sent: Wednesday, January 30, 2002 2:14 PM
To: 'P.Agenbag'; mysql
Subject: RE: SQL help plz
mysql describe mytable;
+---+--+--+-+-+---+
| Field
Further, I'd advise NOT using field names like date and key. Using
eserved words is never a good prcatice.
-Original Message-
From: Rick Emery
Sent: Wednesday, January 30, 2002 2:16 PM
To: 'P.Agenbag'; 'mysql'
Subject: RE: SQL help plz
Sorry, I meant:
mysql select name,max(dateq
Try this:
select key_col, min(name), max(date_col)
from my_table
group by key_col ;
You could use max(name) instead of min(name) also, although
since the names can be misspelled, I don't see why it would matter
which name is displayed.
s.s.
On Wed, 30 Jan 2002
SELECT * FROM db ORDER BY rand() LIMIT 5
--zak
- Original Message -
From: Webmaster [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, May 08, 2001 9:38 PM
Subject: SQL help
How do I write SQL in MySQL to randomly select 5 records from a table?
i think it is more suitable to do it in programming level in mysql, though I
know there is a function is M$sql server.
if your table schema has a unique id field, before submit a query to DB,
1) select count(*) from it
2) from the above resultset, program to draw whatever number of records you
i dont follow step #3.
At 11:42 PM 5/8/2001, Tony Shiu wrote:
i think it is more suitable to do it in programming level in mysql, though I
know there is a function is M$sql server.
if your table schema has a unique id field, before submit a query to DB,
1) select count(*) from it
2) from
using primary key of your table.
mine works in both old and new versions.
- Original Message -
From: Webmaster [EMAIL PROTECTED]
To: Tony Shiu [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, May 09, 2001 12:27 PM
Subject: Re: SQL help
i dont follow step #3.
At 11:42 PM 5/8
You might be able to do something like this instead:
SELECT *, id*0+RAND() AS r FROM table ORDER BY r LIMIT 5;
Check the list archives, this issue comes up quite often.
On Wed, 9 May 2001, Webmaster wrote:
i dont follow step #3.
At 11:42 PM 5/8/2001, Tony Shiu wrote:
i think it is
"j.urban" writes:
select field1,field2,DATE_FORMAT(datefield,"%M %d, %Y"),field4,fieldn from
table;
He didn't want to have to explicitly list all 40 other fields, though.
Can't he do something like
SELECT *.DATE_FORMAT(datefield, "%M %d, %Y") FROM table;
? That gives him one extra field,
53 matches
Mail list logo
