Solved

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

Posted on 2012-04-09
2
386 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 500 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

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

707 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