fosiul01
asked on
need help to join mysql query..(pure mysql query)
hi,
bellow statement will give output like this
mysql> SELECT THREAD_ID, EVENT_ID, EVENT_NAME, SOURCE, TIMER_START, OBJECT_INSTANCE_BEGIN, OPERATION FROM EVENTS_WAITS_CURRENT WHERE THREAD_ID IN(SELECT LOCKED_BY_THREAD_ID FROM MUTEX_INSTANCES WHERE LOCKED_BY_THREAD_ID IS NOT NULL)\G
************************** * 1. row ************************** *
THREAD_ID: 126
EVENT_ID: 605760540
EVENT_NAME: wait/io/file/innodb/innodb _data_file
SOURCE: fil0fil.c:4509
TIMER_START: 5985772299618636
OBJECT_INSTANCE_BEGIN: 139829830175456
OPERATION: write
1 row in set (0.44 sec)
now if i run another query ..
desc threads;
+----------------+-------- ------+--- ---+-----+ ---------+ -------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------- ------+--- ---+-----+ ---------+ -------+
| THREAD_ID | int(11) | NO | | NULL | |
| PROCESSLIST_ID | int(11) | YES | | NULL | |
| NAME | varchar(128) | NO | | NULL | |
+----------------+-------- ------+--- ---+-----+ ---------+ -------+
3 rows in set (0.00 sec)
mysql> select processlist_id from threads where thread_id=126
-> ;
+----------------+
| processlist_id |
+----------------+
| 109 |
+----------------+
1 row in set (0.00 sec)
now what i want is :
insteads of run 2 different query, how can i add 2nd select statement in to first statement so that it add process_list in to the first query's output l.. . so when i execute first query , the output will be :
************************** * 1. row ************************** *
THREAD_ID: 126
EVENT_ID: 605760540
processlist_id:109
EVENT_NAME: wait/io/file/innodb/innodb _data_file
SOURCE: fil0fil.c:4509
TIMER_START: 5985772299618636
OBJECT_INSTANCE_BEGIN: 139829830175456
OPERATION: write
Thanks for your help..
bellow statement will give output like this
mysql> SELECT THREAD_ID, EVENT_ID, EVENT_NAME, SOURCE, TIMER_START, OBJECT_INSTANCE_BEGIN, OPERATION FROM EVENTS_WAITS_CURRENT WHERE THREAD_ID IN(SELECT LOCKED_BY_THREAD_ID FROM MUTEX_INSTANCES WHERE LOCKED_BY_THREAD_ID IS NOT NULL)\G
**************************
THREAD_ID: 126
EVENT_ID: 605760540
EVENT_NAME: wait/io/file/innodb/innodb
SOURCE: fil0fil.c:4509
TIMER_START: 5985772299618636
OBJECT_INSTANCE_BEGIN: 139829830175456
OPERATION: write
1 row in set (0.44 sec)
now if i run another query ..
desc threads;
+----------------+--------
| Field | Type | Null | Key | Default | Extra |
+----------------+--------
| THREAD_ID | int(11) | NO | | NULL | |
| PROCESSLIST_ID | int(11) | YES | | NULL | |
| NAME | varchar(128) | NO | | NULL | |
+----------------+--------
3 rows in set (0.00 sec)
mysql> select processlist_id from threads where thread_id=126
-> ;
+----------------+
| processlist_id |
+----------------+
| 109 |
+----------------+
1 row in set (0.00 sec)
now what i want is :
insteads of run 2 different query, how can i add 2nd select statement in to first statement so that it add process_list in to the first query's output l.. . so when i execute first query , the output will be :
**************************
THREAD_ID: 126
EVENT_ID: 605760540
processlist_id:109
EVENT_NAME: wait/io/file/innodb/innodb
SOURCE: fil0fil.c:4509
TIMER_START: 5985772299618636
OBJECT_INSTANCE_BEGIN: 139829830175456
OPERATION: write
Thanks for your help..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
its saying :
SELECT ewc.THREAD_ID, ewc.EVENT_ID, threads.processlist_id, ewc.EVENT_NAME, ewc.SOURCE, ewc.TIMER_START, ewc.OBJECT_INSTANCE_BEGIN,
ERROR 1052 (23000): Column 'THREAD_ID' in IN/ALL/ANY subquery is ambiguous