CODEWITHSUNDEEP
phpmysql
user2469520
user2469520

Reputation:

Syntax error with unioned tables

This query works...

$stm = $pdo->prepare("SELECT T1.Taxon, T1.Rank, T2.Taxon, T2.Rank FROM     gz_life_reptiles_new T1
LEFT JOIN gz_life_reptiles_new T2 ON T2.Taxon = T1.Taxon
UNION
SELECT T1.Taxon, T1.Rank, T2.Taxon, T2.Rank FROM gz_life_reptiles_new T1
RIGHT JOIN gz_life_reptiles T2 ON T2.Taxon = T1.Taxon");
 $stm->execute(array(
));

However, I want to combine them into a single virtual table with the name "combined" so I can perform more operations, including GROUP BY, something like this...

$stm = $pdo->prepare("SELECT *
FROM (SELECT T1.Taxon, T1.Rank, T2.Taxon, T2.Rank FROM  gz_life_reptiles_new T1
LEFT JOIN gz_life_reptiles_new T2 ON T2.Taxon = T1.Taxon
UNION
SELECT T3.Taxon, T3.Rank, T4.Taxon, T4.Rank FROM gz_life_reptiles_new T3
RIGHT JOIN gz_life_reptiles T4 ON T4.Taxon = T3.Taxon
) AS Combined
GROUP BY Taxon");
 $stm->execute(array(
));

However, I didn't write it correctly. I get the error message #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS Combined GROUP BY Taxon LIMIT 0, 30' at line 6

Can anyone see what I'm doing wrong?

Upvotes: 0

Views: 24

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

MySQL doesn't allow subqueries in the from clause for a view. So, just do:

SELECT T1.Taxon, T1.Rank, T2.Taxon, T2.Rank
FROM  gz_life_reptiles_new T1 LEFT JOIN
      gz_life_reptiles_new T2
      ON T2.Taxon = T1.Taxon
UNION
SELECT T3.Taxon, T3.Rank, T4.Taxon, T4.Rank
FROM gz_life_reptiles_new T3 RIGHT JOIN
     gz_life_reptiles T4
     ON T4.Taxon = T3.Taxon;

The distinct probably does what you want for removing duplicates. If not, then you have a challenge.

EDIT:

Okay, here is another method:

SELECT T1.Taxon, T1.Rank, T2.Taxon, T2.Rank
FROM  gz_life_reptiles_new T1 LEFT JOIN
      gz_life_reptiles_new T2
      ON T2.Taxon = T1.Taxon
UNION ALL
SELECT T3.Taxon, T3.Rank, T4.Taxon, T4.Rank
FROM gz_life_reptiles_new T3 RIGHT JOIN
     gz_life_reptiles T4
     ON T4.Taxon = T3.Taxon
WHERE NOT EXISTS (SELECT 1
                  FROM gz_life_reptiles_new T1
                  WHERE T4.Taxon = T1.Taxon
                 );

By the way, do you intend for the first LEFT JOIN to be on gz_life_reptiles_new twice? The structure of your query is usually to approximate a FULL OUTER JOIN and the structure is: "FROM A LEFT JOIN B UNION ALL FROM B LEFT JOIN A". That is, the same tables are in both queries.

Upvotes: 1

Related Questions