Link to home
Start Free TrialLog in
Avatar of fosiul01
fosiul01Flag for United Kingdom of Great Britain and Northern Ireland

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..
ASKER CERTIFIED SOLUTION
Avatar of Scott Madeira
Scott Madeira
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fosiul01

ASKER

hi thanks

its saying :
 SELECT ewc.THREAD_ID, ewc.EVENT_ID,  threads.processlist_id, ewc.EVENT_NAME, ewc.SOURCE, ewc.TIMER_START, ewc.OBJECT_INSTANCE_BEGIN, ewc.OPERATION  FROM EVENTS_WAITS_CURRENT ewc left join threads on ewc.thread_id = threads.thread_id  WHERE THREAD_ID IN(SELECT LOCKED_BY_THREAD_ID FROM MUTEX_INSTANCES WHERE LOCKED_BY_THREAD_ID IS NOT NULL);
ERROR 1052 (23000): Column 'THREAD_ID' in IN/ALL/ANY subquery is ambiguous