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
tab
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 B
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 li
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
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 g
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 tablena
[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]
http://dev.mysql.com/doc/mysql/en
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(Gi
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 anyt
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:
in question below, the problem is not in record '7047' but in
the
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:
> in question below, the problem is not in record '7047' but
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
Subject: SQ
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 audit_log
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
audit_log.
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
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 car
Carl Schéle, IT, Posten wrote:
I got a table, champions, looking like this:
id winner_1 winner_2
0carl mattias
1daniel carl
2er
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, champio
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 w
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 lin
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 16,
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
have
* 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 th
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]
d then apply the max
> function
> to the integer values to get correct results.
>
> Best regards,
>
> Lin
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 24, 2003 4:41 PM
> To: Dathan Vance Pattisha
age-
From: [EMAIL 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 on
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 d
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 som
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 entir
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 h
-->-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
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
* 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
only 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
SELECT UserID, WeekID, SUM(Points) AS WeeklyPoints FROM History GROUP BY
WeekID;
Gurhan
-Original Message-
From: Dave [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 23, 2002 9:37 AM
To: [EMAIL PROTECTED]
Subject: SQL Help Needed
I have 2 tables
Users (UserID)
History (UserID, WeekID
* 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
>
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
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 h
re cups
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, su
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. Suc
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
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,
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 21:36:04
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
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>
mysql> describe mytable;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| id| int(11) | YES | | NULL| |
| keyq | int(11) | YES | | NULL| |
| name | cha
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
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 i
t;
rand() seems 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
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)
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
wa
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?
>
>
> --
Cindy writes:
>SELECT *.DATE_FORMAT(datefield, "%M %d, %Y") FROM table;
Agh. *,DATE of course. Comma, not dot.
--Cindy
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysq
"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,
Mike Podlesny wrote:
>
> I want to select all the fields in from the mySQL database except I want my
> date field to be returned in the format of:
>
> January 16, 2001
>
> How can this be done without having to do the select in this manner:
>
> SELECT field1,field2,MONTHNAME(datefield),DA
select field1,field2,DATE_FORMAT(datefield,"%M %d, %Y"),field4,fieldn from
table;
See the MySQL Manual:
http://www.mysql.com/doc/D/a/Date_and_time_functions.html
On Tue, 16 Jan 2001, Mike Podlesny wrote:
> I want to select all the fields in from the mySQL database except I want my
> date field
Check in the manual about the DATE_FORMAT function
HTH
Quentin
-Original Message-
From: Mike Podlesny [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 17 January 2001 07:55
To: mySQL Mailing List
Subject: SQL HELP
I want to select all the fields in from the mySQL database except I want my
d
On Tue, Jan 16, 2001 at 01:55:01PM -0500, Mike Podlesny wrote:
> I want to select all the fields in from the mySQL database except I want my
> date field to be returned in the format of:
>
> January 16, 2001
>
> How can this be done without having to do the select in this manner:
>
> SELECT fie
59 matches
Mail list logo