CODEWITHSUNDEEP
mysql
user3802162
user3802162

Reputation: 23

Mysql subquery returns incorrect results

I know this is a very stupid thing to ask, and believe me I tried everything. This is my last resort.

I am trying to run a subquery. Surprisingly mysql hangs (even though its not a big query).

I am trying to run this query to return me a list of Link_ids

    select distinct link_id from join_link_object where obj_id = (select group_concat(obj_id) from xalt_object where module_name like '%intel-compilers/2016.0.047%');

+---------+
| link_id |
+---------+
|  153249 |
+---------+

The result returned is not correct. Instead of returning the list of link_id query returns just the first one.

I know using = is not a good idea, I tried using IN but the prompt just hangs and returns nothing.

Although running subquery separately and then running the main query with its results gives correct results (verified manually).

mysql> select group_concat(obj_id) from xalt_object where module_name like '%intel-compilers/2016.0.047%';
+-------------------------------------------+
| group_concat(obj_id)                      |
+-------------------------------------------+
| 352304,352305,352306,352307,352308,354813 |
+-------------------------------------------+

then running main query - 

mysql> select distinct link_id from join_link_object where obj_id in (352304,352305,352306,352307,352308,354813);
+---------+
| link_id |
+---------+
|  153249 |
|  153467 |
|  153996 |
|  154170 |
|  155077 |
|  155099 |
|  155100 |
+---------+

I even tried using EXISTS, but these query returns absurd results (around 156995 rows)-

select distinct link_id from join_link_object where EXISTS  (select obj_id from xalt_object where module_name like '%intel-compilers/2016.0.047%');

Please advice! Thanks

Upvotes: 1

Views: 139

Answers (1)

Jonathan Solorzano
Jonathan Solorzano

Reputation: 7022

In the first query you're comparing the obj_id with the subquery result, when you should be filtering the obj_id in the subquery result, as you did in the test: 

select distinct link_id 
from join_link_object 
where obj_id in (352304,352305,352306,352307,352308,354813);

Here's what it should be:

select distinct link_id 
from join_link_object 
where obj_id 
in (select obj_id from xalt_object where module_name like '%intel-compilers/2016.0.047%');

To improve performance try this out:

SELECT DISTINCT jlo.link_id 
FROM join_link_object AS jlo 
INNER JOIN xalt_object AS xo 
ON (jlo.obj_id = xo.obj_id) 
WHERE xo.module_name LIKE '%intel-compilers/2016.0.047%';

Upvotes: 2

Related Questions