CODEWITHSUNDEEP
mysqlsql
James Mnatzaganian
James Mnatzaganian

Reputation: 1285

MySQL inner join on subquery

I'm trying to construct an inner join on a subquery. I keep getting an error saying that it can't reopen table "t1".

This is what I'm trying to do in plain English:

select all instances of "SystemHeader_Name" from "temp2" where "SystemHeader_Name" and "System_Value" are shared across "Tool_Name"

This is my attempt:

SELECT t1.SystemHeader_Name FROM temp2 t1
INNER JOIN (
    SELECT DISTINCT Tool_Name, SystemHeader_Name, System_Value FROM temp2
) AS t2 ON (t2.SystemHeader_Name = t1.SystemHeader_Name 
    AND t2.System_Value = t1.System_Value);

How do I accomplish this?

Example
With:

Tool_Name,SystemHeader_Name,System_Value
t1,h1,v1
t1,h2,v2
t2,h1,v1

The result should be:

h1

Issue
After some more digging, I determined that my issue was with the temporary table. From this document: You cannot refer to a TEMPORARY table more than once in the same query.

It looks like I'll need to come up with a better method than using temporary tables. Thank you all for your help.

Upvotes: 1

Views: 136

Answers (2)

collusionbdbh
collusionbdbh

Reputation: 701

This should do it:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT DISTINCT t1.SystemHeader_Name
FROM temp2 t1
JOIN temp2 t2
    ON t2.SystemHeader_Name = t1.SystemHeader_Name
    AND t2.System_Value = t1.System_Value
    AND t2.Tool_Name <> t1.Tool_Name

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15379

Try this:

SELECT distinct t1.SystemHeader_Name
FROM temp2 t1
where exists(
    SELECT 'X'
    FROM temp2 t2
    WHERE t2.system_value = t1.system_value
    AND t2.tool_name <> t1.tool_name
    AND t2.systemheader_name = t1.systemheader_name
)

I use exists instead join because you don't want all rows but one if exists another systemheader

Tell me if accomplish your task.

Upvotes: 1

Related Questions