• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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..
0
fosiul01
Asked:
fosiul01
1 Solution
 
Scott MadeiraCommented:
Try this....

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)
0
 
fosiul01Author Commented:
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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now