please help reading DB deadlock notice
Hi, I'd really appreciate help with reading this db deadlock notice. Essentially, I'd like to understand: -what holds the lock that TX2 is waiting on index `token` of table `eviltwin/user_sessions`? I thought it'd be TX1 (hence deadlock ?), yet it appears that TX1 holds no locks -if the answer to the above cannot be found in attached report, is there a setting I should turn on the server to get the necessary info next time this deadlock occurs? Our setup: MySQL 5, InnoDB, Repeatable Read relevant columns in table user_sessions: -id (PK) -token (unique, nullable) -serverSessionId (FK, nullable) TX1 does: -select for update on a given 'token' -set serverSessionId to null TX2 does: -select for update on the same 'token' -delete selected record TX2 seems to start a little earlier, and succeed on 'select for update'. TX1 then tries to do the same and is forced to wait on a lock. This I understand. But why does TX2 need to wait on a lock to deleted already 'selected for update' user_session? Who's holding that lock? thanks, -nikita *** 1. row *** Status: = 070402 12:24:38 INNODB MONITOR OUTPUT = Per second averages calculated from the last 29 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 6313003, signal count 5099409 Mutex spin waits 819355967, rounds 1424470313, OS waits 4424501 RW-shared spins 1000685, OS waits 629116; RW-excl spins 760423, OS waits 175362 LATEST DETECTED DEADLOCK 070402 12:22:41 *** (1) TRANSACTION: TRANSACTION 0 12012950, ACTIVE 0 sec, process no 7328, OS thread id 1161120096 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1216 MySQL thread id 93849, query id 1913636508 lf20.eviltwinstudios.net 192.168.100.163 eviltwin Sending data select usersessio0_.id as id37_, usersessio0_.hibernateVersion as hibernat2_37_, usersessio0_.serverSessionId as serverSe3_37_, usersessio0_.userId as userId37_, usersessio0_.loginTime as loginTime37_, usersessio0_.logoutTime as logoutTime37_, usersessio0_.boot as boot37_, usersessio0_.token as token37_, usersessio0_.macAddressMD5 as macAddre9_37_, usersessio0_.expirationTime as expirat10_37_, usersessio0_.creationDate as creatio11_37_ from user_sessions usersessio0_ where usersessio0_.token='82ff6193-1216-449a-9e33-5426fb8e10ef' for update *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 130062 n bits 144 index `PRIMARY` of table `eviltwin/user_sessions` trx id 0 12012950 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 13; compact format; info bits 32 0: len 30; hex 37653530373061382d393537362d343363352d623138342d656266633763; asc 7e5070a8-9576-43c5-b184-ebfc7c;...(truncated); 1: len 6; hex 00b74d95; asc M ;; 2: len 7; hex 00803c29ee; asc ) ;; 3: len 4; hex 8001; asc ;; 4: len 30; hex 39373939383430622d323938372d346630382d613539342d663736303831; asc 9799840b-2987-4f08-a594-f76081;...(truncated); 5: len 30; hex 34343431316237362d386663312d343332652d386332342d653837646433; asc 44411b76-8fc1-432e-8c24-e87dd3;...(truncated); 6: len 8; hex 80001241013060af; asc A 0` ;; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 30; hex 3832363139332d313231362d343439612d39652d35343232; asc 82ff6193-1216-449a-9e33-5426fb;...(truncated); 10: len 30; hex 62653637616563323661316363613261646561616135373430646130; asc be67aec26a1cca2adeffaaa5740da0;...(truncated); 11: len 8; hex 80001241014ee557; ascA N W;; 12: len 8; hex 80001241013060af; ascA 0` ;; *** (2) TRANSACTION: TRANSACTION 0 12012949, ACTIVE 0 sec, process no 7328, OS thread id 1182153056 updating or deleting, thread declared inside InnoDB 499 mysql tables in use 1, locked 1 8 lock struct(s), heap size 1216, undo log entries 3 MySQL thread id 93773, query id 1913636516 lf20.eviltwinstudios.net 192.168.100.163 eviltwin updating delete from user_sessions where id='7e5070a8-9576-43c5-b184-ebfc7c288d69' and hibernateVersion=1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 130062 n bits 144 index `PRIMARY` of table `eviltwin/user_sessions` trx id 0 12012949 lock_mode X locks rec but not gap Record lock, heap no 9 PHYSICAL RECORD: n_fields 13; compact format; info bits 32 0: len 30; hex 37653530373061382d393537362d343363352d623138342d656266633763; asc 7e5070a8-9576-43c5-b184-ebfc7c;...(truncated); 1: len 6; hex 00b74d95; asc M ;; 2: len 7; hex 00803c29ee; asc ) ;; 3: len 4; hex 8001; asc ;; 4: len 30; hex 39373939383430622d323938372d346630382d613539342d663736303831; asc 9799840b-2987-4f08-a594-f76081;...(truncated); 5: len 30; hex 34343431316237362d386663312d343332652d386332342d653837646433; asc 44411b76-8fc1-432e-8c24-e87dd3;...(truncated); 6: len 8; hex 80001241013060af; asc
Re: Database Replication Fallover
Hi Scott, Thanks for the information. I'll look into linux-ha seriously. Regards, Ben Scott Tanner wrote: Ben, Check out Linux-ha ( http://linux-ha.org). We are using version 1 at my company, which is fairly easy to set up and there are a lot of good articles on it. With this you can create a virtual address to fail-over between systems, run commands / scripts during a fail over, and even kill the other node (stonith) if you have supporting hardware. For our setup, we have the client apps accessing the db through the virtual IP address. We have 2 mysql config files, one for the master and one for the slave (actually we have one m4'd config file, but you get the idea). During failover, we restart mysql on the slave using the master config file and transfer the virtual IP over. Here are a couple of key points for setting this up: 1. Slave must be running with the 'log-slave-updates' option. 2. After you run a back issue a reset master on the slave server. Hope this helps, Scott Tanner On Mon, Apr 02, 2007 at 08:43:35AM +0100, Ben Clewett wrote: I forgot to mention that I am running Linux. If anybody has some idea of software which can do this, I'd be very interested. Regards, Ben Ben Clewett wrote: Dear MySql, I'm looking into availability and wonder if any member might be able to help me. I have two databases, one Primary and one full Replication. Normally the primary is used for data input, reports are drawn from the replication. If I loose the Primary, do any members have any software they can recommend which: - Stops the replication daemon. - Sets the replication server to Read/Write. - Shuts down the primary. - Routes traffic to the replication. Any advise or ideas would be very useful... Regards, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert data in to columns base on the selection of the list box.
Can you help me please?
1-I have created a while loop to populate the list box with the information
of column1.
2-I need to update (insert data) in to column2, column3, column4 base
on the selection of the list box.
echo'form';
$query = SELECT column1 FROM table;
$result = mysql_query($query);
echobr;
echobr;
echocenter;
echoselect NAME='R';
echooption value='NULL'Choose a Category:/option
;
while ($line = mysql_fetch_array($result))
{
foreach ($line as $value)
{
echoOPTION value='$value';
}
echo $value/OPTION;
}
echo /select;
echo /form;
$sql=INSERT INTO table WHERE column1='.$_POST[R].'
(column2, column3, column4)VALUES('info2', 'info3', 'info4');
$result=mysql_query($sql);
-
No need to miss a message. Get email on-the-go
with Yahoo! Mail for Mobile. Get started.
mysql question.
hi i need help regarding a sql query in my php app. the query is : $SQL = SELECT DISTINCT(EMail) FROM mena_guests WHERE Voted = 'yes' LIMIT $startingID,$items_numbers_list; i want to sort this query by the number of the repeated EMail counts. can anyone help me with that please ?
Re: mysql question.
Hmmm, using distinct email in a query where you want to count email won't work. select mail, count(mail) as mailcnt from guest where voted='yes' group by mail order by mailcnt; should do the trick. On Tue, April 3, 2007 12:51, Me2resh Lists wrote: hi i need help regarding a sql query in my php app. the query is : $SQL = SELECT DISTINCT(EMail) FROM mena_guests WHERE Voted = 'yes' LIMIT $startingID,$items_numbers_list; i want to sort this query by the number of the repeated EMail counts. can anyone help me with that please ? -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help reading DB deadlock notice
Nikita Tovstoles [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Do both of your SELECT statements use the same index (`token`) when setting locks on the rows? My guess is that the situation is as follows. TX2: SELECT ... WHERE id = ... FOR UPDATE TX2 sets an exclusive lock for a row on PRIMARY index. TX1: SELECT ... WHERE token = ... FOR UPDATE TX1 tries to acquire X-lock for a row in `token` index, which succedes. This however requires also a lock on PRIMARY, but the corresponding row on that index is already locked by TX2. TX1 waits. TX2: DELETE ... WHERE id = ... TX2 tries to delete the row. This of course requires the removal of its presence in the indxexes, including `token`, but because of the earlier exclusive lock set by TX1 this cannot be accomplished without waiting for the lock to be freed. TX2 waits. Hence, the deadlock. I'm wondering if granting X-locks for the secondary indexes could not be delayed until such lock on the primary key is acquired...? Maciek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Joins versus Grouping/Indexing: Normalization Excessive?
Do you really mean 1:1 relationships? A song might have more than one artist, album, or genre. Could a song have no album at all, such as something that was released in MP3 format? Also, you might have more than one version of a song. You might need a separate table for recordings, with each song having one or more recordings. That would be a good place to keep the release information, rather than storing it with the song. The biggest problem would be to figure out how to index the songs, themselves. I don't have a good suggestion for that off the top of my head. That all being said, there's no reason I can think of not to normalize the data. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Daniel Cousineau [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 12:15 AM To: mysql@lists.mysql.com Subject: Joins versus Grouping/Indexing: Normalization Excessive? So I'm currently designing a database for a web site and intra net for my campuses student radio. Since I'm not getting paid for this and I'm doing this in my free time I kinda want to take my time and have the system setup as perfectly as any one college student can. I'm currently debating on how I want to store all the songs in the system. Namely I'm not quite sure which way will eek the most performance out of MySQL. My plan so far is quite normalized, a songs table with 1:1 relationships with an Albums, Artists, and Genres table. The big benefits I see from this is when I'm building the intra net application I'll want to pull just all of the artists or all of the albums, etc. However I feel like I'm encountering issues with where to store the year field of an mp3 (do I want it on the album, song, or both) along with issues like printing everything out at once. The only other way I can think of thats relatively efficient is to have the singular songs table and have indexes on albums, artists, and genres. My question, more out of curiosity than necessity, is which of these would be more efficient (given that I'll be using the InnoDB storage engine)? Other relevant facts include it'll be using the latest, stable release of MySQL 5 and I'll be developing in PHP5 (through CakePHP's database abstraction layer). -- Thanks and Gig 'Em! Daniel Cousineau http://www.terminalfuture.com/ http://www.linkedin.com/in/dcousineau [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sort Aborted Error
Hi, I am getting the below error in my MySQL err.log file. Can I know the significance of this error and also how can I identify the respective query which caused this error. 070403 6:28:28 [ERROR] folder path : Sort aborted Thanks, Phani
RE: insert data in to columns base on the selection of the list box.
Without knowing where the values of column2, column3, and column4 are coming
from it's a little hard to say what the best technique would be. Usually one
would take the POSTed value from the select control and use it to retrieve
the related data from a table in your data base.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: sam rumaizan [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 6:51 AM
To: mysql@lists.mysql.com
Subject: insert data in to columns base on the selection of
the list box.
Can you help me please?
1-I have created a while loop to populate the list box
with the information of column1.
2-I need to update (insert data) in to column2,
column3, column4 base on the selection of the list box.
echo'form';
$query = SELECT column1 FROM table;
$result = mysql_query($query);
echobr;
echobr;
echocenter;
echoselect NAME='R';
echooption value='NULL'Choose a Category:/option
;
while ($line = mysql_fetch_array($result))
{
foreach ($line as $value)
{
echoOPTION value='$value';
}
echo $value/OPTION;
}
echo /select;
echo /form;
$sql=INSERT INTO table WHERE column1='.$_POST[R].'
(column2, column3, column4)VALUES('info2', 'info3', 'info4');
$result=mysql_query($sql);
-
No need to miss a message. Get email on-the-go
with Yahoo! Mail for Mobile. Get started.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Describing CD contents in a MySQL record
What's is the best way to save CD/DVD tree in a MySQL record ? Knowing that it will be used frequently for displaying as a tree and for searching by file name, file size.. -- View this message in context: http://www.nabble.com/Describing-CD-contents-in-a-MySQL-record-tf3518847.html#a9819829 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT/RIGHT Joins not working
I tried the following 2 SQL's and the results are less than satisfactory. The RIGHT join does not show where disposition is NULL. The LEFT join shows dispositions as NULL where they shouldn't be. Also the LEFT join generates more dupes. Any way to fix this? select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust RIGHT JOIN disposition on (cust.disposition=disposition.id) RIGHT JOIN leadSource on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and cust.disposition 0 order by SOURCE, DISPOSITION select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust LEFT JOIN disposition on (cust.disposition=disposition.id) LEFT JOIN leadSource on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and cust.disposition 0 order by SOURCE, DISPOSITION The MYSQL has the following version mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686) Thanks for your help Murthy - Don't get soaked. Take a quick peek at the forecast with theYahoo! Search weather shortcut.
Re: LEFT/RIGHT Joins not working
a left join and a right join are 2 very distinct things... It is not clear from your text what it is you exactly are going for here but I doubt that applying either LEFT or RIGHT to ALL of your (many) joins is going to give it to you. You need to stop and examine the relationships between the tables in this query and determine which class of JOIN you will need (and there are more than just these 2). The description you gave of your results using RIGHT and LEFT universally are consistent with what I would expect from those types of joins. I suggest that you read this page very carefully before you continue: http://dev.mysql.com/doc/refman/5.1/en/join.html - michael On 4/3/07, murthy gandikota [EMAIL PROTECTED] wrote: I tried the following 2 SQL's and the results are less than satisfactory. The RIGHT join does not show where disposition is NULL. The LEFT join shows dispositions as NULL where they shouldn't be. Also the LEFT join generates more dupes. Any way to fix this? select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust RIGHT JOIN disposition on (cust.disposition=disposition.id) RIGHT JOIN leadSource on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and cust.disposition 0 order by SOURCE, DISPOSITION select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust LEFT JOIN disposition on (cust.disposition=disposition.id) LEFT JOIN leadSource on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and cust.disposition 0 order by SOURCE, DISPOSITION The MYSQL has the following version mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686) Thanks for your help Murthy - Don't get soaked. Take a quick peek at the forecast with theYahoo! Search weather shortcut. -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert data in to columns base on the selection of the list box.
Can you tell us what exactly is your problem ? ie returned error, logic,
or what ?
My first impression es that your insert is wrong, because inserts cant
have where conditions (it makes no sense) probably you want to do a
completely new insert including the column1 or maybe you want an
update. Anywhere you can check the manual for sintax issues.
Carlos
sam rumaizan wrote:
Can you help me please?
1-I have created a while loop to populate the list box with the information
of column1.
2-I need to update (insert data) in to column2, column3, column4 base
on the selection of the list box.
echo'form';
$query = SELECT column1 FROM table;
$result = mysql_query($query);
echobr;
echobr;
echocenter;
echoselect NAME='R';
echooption value='NULL'Choose a Category:/option
;
while ($line = mysql_fetch_array($result))
{
foreach ($line as $value)
{
echoOPTION value='$value';
}
echo $value/OPTION;
}
echo /select;
echo /form;
$sql=INSERT INTO table WHERE column1='.$_POST[R].'
(column2, column3, column4)VALUES('info2', 'info3', 'info4');
$result=mysql_query($sql);
-
No need to miss a message. Get email on-the-go
with Yahoo! Mail for Mobile. Get started.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT/RIGHT Joins not working
Hi Mike
Thanks for your input. I read the page before and it caused more confusion.
May be an example would clarify this:
create table `cust` (`ssn` varchar(10), `source` int(3) , `disposition`
int(3));
insert into cust (ssn, source, disposition) values ('123456789', 1, 2);
insert into cust (ssn, source, disposition) values ('123456780', 2, 1);
insert into cust (ssn, source, disposition) values ('123456781', NULL, NULL);
create table `source` (`id` int(3), `source` varchar(10));
insert into source(id, source) values (1, 'source1');
insert into source(id,source) values (2, 'source2');
create table `disposition` (`id` int(3), `disposition` varchar(10));
insert into disposition (id, disposition) values (1, 'dispo1');
insert into disposition(id,disposition) values (2, 'dispo2');
Now I run the sql:
select cust.ssn, disposition.disposition, source.source from cust LEFT JOIN
source ON (cust.source=source.id) LEFT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL| NULL|
+---+-+-+
I don't want this cos the ssn 123456780 is shown twice
select cust.ssn, disposition.disposition, source.source from cust RIGHT JOIN
source ON (cust.source=source.id) RIGHT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
+---+-+-+
This has the same problem.
All I want is
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL| NULL|
+---+-+-+
I'd appreciate your help.
Thanks
Murthy
Michael Dykman [EMAIL PROTECTED] wrote:
a left join and a right join are 2 very distinct things... It is not
clear from your text what it is you exactly are going for here but I
doubt that applying either LEFT or RIGHT to ALL of your (many) joins
is going to give it to you. You need to stop and examine the
relationships between the tables in this query and determine which
class of JOIN you will need (and there are more than just these 2).
The description you gave of your results using RIGHT and LEFT
universally are consistent with what I would expect from those types
of joins. I suggest that you read this page very carefully before you
continue:
http://dev.mysql.com/doc/refman/5.1/en/join.html
- michael
On 4/3/07, murthy gandikota wrote:
I tried the following 2 SQL's and the results are less than satisfactory. The
RIGHT join does not show where disposition is NULL. The LEFT join shows
dispositions as NULL where they shouldn't be. Also the LEFT join generates
more dupes. Any way to fix this?
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN,
disposition.disposition as DISPOSITION, leadSource.source as SOURCE,
cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT,
cust.newPayment as NEWPAYMENT from cust RIGHT JOIN disposition on
(cust.disposition=disposition.id) RIGHT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN,
disposition.disposition as DISPOSITION, leadSource.source as SOURCE,
cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT,
cust.newPayment as NEWPAYMENT from cust LEFT JOIN disposition on
(cust.disposition=disposition.id) LEFT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
The MYSQL has the following version
mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)
Thanks for your help
Murthy
-
Don't get soaked. Take a quick peek at the forecast
with theYahoo! Search weather shortcut.
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
-
8:00? 8:25? 8:40? Find a flick in no time
with theYahoo! Search movie showtime shortcut.
Re: LEFT/RIGHT Joins not working
Well, trying your example gives me the expected result.
select cust.ssn, disposition.disposition, source.source
from cust LEFT JOIN source ON (cust.source=source.id)
LEFT JOIN disposition ON (cust.disposition=disposition.id);
'123456789', 'dispo2', 'source1'
'123456780', 'dispo1', 'source2'
'123456781', null, null
On Wed, April 4, 2007 00:03, murthy gandikota wrote:
Hi Mike
Thanks for your input. I read the page before and it caused more
confusion.
May be an example would clarify this:
create table `cust` (`ssn` varchar(10), `source` int(3) , `disposition`
int(3));
insert into cust (ssn, source, disposition) values ('123456789', 1, 2);
insert into cust (ssn, source, disposition) values ('123456780', 2, 1);
insert into cust (ssn, source, disposition) values ('123456781', NULL,
NULL);
create table `source` (`id` int(3), `source` varchar(10));
insert into source(id, source) values (1, 'source1');
insert into source(id,source) values (2, 'source2');
create table `disposition` (`id` int(3), `disposition` varchar(10));
insert into disposition (id, disposition) values (1, 'dispo1');
insert into disposition(id,disposition) values (2, 'dispo2');
Now I run the sql:
select cust.ssn, disposition.disposition, source.source from cust LEFT
JOIN source ON (cust.source=source.id) LEFT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL| NULL|
+---+-+-+
I don't want this cos the ssn 123456780 is shown twice
select cust.ssn, disposition.disposition, source.source from cust RIGHT
JOIN source ON (cust.source=source.id) RIGHT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
+---+-+-+
This has the same problem.
All I want is
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL| NULL|
+---+-+-+
I'd appreciate your help.
Thanks
Murthy
Michael Dykman [EMAIL PROTECTED] wrote:
a left join and a right join are 2 very distinct things... It is not
clear from your text what it is you exactly are going for here but I
doubt that applying either LEFT or RIGHT to ALL of your (many) joins
is going to give it to you. You need to stop and examine the
relationships between the tables in this query and determine which
class of JOIN you will need (and there are more than just these 2).
The description you gave of your results using RIGHT and LEFT
universally are consistent with what I would expect from those types
of joins. I suggest that you read this page very carefully before you
continue:
http://dev.mysql.com/doc/refman/5.1/en/join.html
- michael
On 4/3/07, murthy gandikota wrote:
I tried the following 2 SQL's and the results are less than
satisfactory. The RIGHT join does not show where disposition is NULL.
The LEFT join shows dispositions as NULL where they shouldn't be. Also
the LEFT join generates more dupes. Any way to fix this?
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as
SSN, disposition.disposition as DISPOSITION, leadSource.source as
SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as
CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust RIGHT JOIN
disposition on (cust.disposition=disposition.id) RIGHT JOIN leadSource
on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as
SSN, disposition.disposition as DISPOSITION, leadSource.source as
SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as
CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust LEFT JOIN
disposition on (cust.disposition=disposition.id) LEFT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
The MYSQL has the following version
mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)
Thanks for your help
Murthy
-
Don't get soaked. Take a quick peek at the
