need help to join 3 mysql query in one(pure mysql query)(urgent)

Ref: unsolved solution:http://www.experts-exchange.com/Database/MySQL/Q_27667370.html

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..
LVL 29
fosiul01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AegilCommented:
SELECT EVENTS_WAITS_CURRENT .THREAD_ID, threads.processlist_id, EVENTS_WAITS_CURRENT .EVENT_ID, EVENTS_WAITS_CURRENT .EVENT_NAME, EVENTS_WAITS_CURRENT .SOURCE, EVENTS_WAITS_CURRENT .TIMER_START, EVENTS_WAITS_CURRENT .OBJECT_INSTANCE_BEGIN, EVENTS_WAITS_CURRENT .OPERATION FROM EVENTS_WAITS_CURRENT, threads WHERE THREAD_ID IN(SELECT LOCKED_BY_THREAD_ID FROM MUTEX_INSTANCES WHERE LOCKED_BY_THREAD_ID IS NOT NULL) AND threads.THREAD_ID=EVENTS_WAITS_CURRENT.THREAD_ID\G

Open in new window


something like that should work.
0
fosiul01Author Commented:
hi but it saying

ERROR 1052 (23000): Column 'THREAD_ID' in IN/ALL/ANY subquery is ambiguous
0
AegilCommented:
SELECT EVENTS_WAITS_CURRENT .THREAD_ID, threads.processlist_id, EVENTS_WAITS_CURRENT .EVENT_ID, EVENTS_WAITS_CURRENT .EVENT_NAME, EVENTS_WAITS_CURRENT .SOURCE, EVENTS_WAITS_CURRENT .TIMER_START, EVENTS_WAITS_CURRENT .OBJECT_INSTANCE_BEGIN, EVENTS_WAITS_CURRENT .OPERATION FROM EVENTS_WAITS_CURRENT, threads WHERE EVENTS_WAITS_CURRENT.THREAD_ID IN(SELECT LOCKED_BY_THREAD_ID FROM MUTEX_INSTANCES WHERE LOCKED_BY_THREAD_ID IS NOT NULL) AND threads.THREAD_ID=EVENTS_WAITS_CURRENT.THREAD_ID\G

Open in new window


Try the above revision, you just need to specify the table.field in each select where you are selecting from multiple tables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

fosiul01Author Commented:
thats great thanks , it works..

is there any way to join those table via some other join example : left or right ???
or thats the only way i can join ??
0
AegilCommented:
yeah, you should be able to do left / right joins,

I guess it would be from threads left join threads on threads.THREAD_ID=EVENTS_WAITS_CURRENT.THREAD_ID
or something similar. It depends on how your database is setup.  I'm not great on the joining config though myself, if you need further help on that maybe there are some other guys with better db knowledge here .
0
Ray PaseurCommented:
[ERROR 1052 (23000): Column 'THREAD_ID' in IN/ALL/ANY subquery is ambiguous
This may be solved by using the table.column AS name notation when referring to the ambiguous columns.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.