?
Solved

need help to join mysql query..(pure mysql query)

Posted on 2012-04-09
2
Medium Priority
?
393 Views
Last Modified: 2012-04-18
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
Comment
Question by:fosiul01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 14

Accepted Solution

by:
Scott Madeira earned 1500 total points
ID: 37823639
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
 
LVL 29

Author Comment

by:fosiul01
ID: 37824154
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question