Intersect command is not working

Question

MariaDB [object]> select Protein, count(mirna) from exp2 
INTERSECT select Protein, count(mirna) from exp3 group by Protein;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select Protein, count(mirna) from exp3 group by Protein' at line 1.

I have two tables exp2 and exp3, both have many common rows, I want to query from the common data from these two tables.i.e. I want have a common data table of Protein and corresponding count of miRNAs in number.

I am using lampp, how I can resolve this query?

thanks for editing the format of question. – user7108953 Nov 30 '16 at 11:49 — user7108953, Nov 30 '16 at 11:49

score 1 · Answer 1 · answered Nov 30 '16 at 12:11

MySQL doesn't offer the INTERSECT operation. You need to use something like a JOIN operation. This example suppresses all rows that don't match the ON condition.

SELECT a.Protein, a.mirnacount
  FROM (SELECT Protein, count(mirna) mirnacount from exp2 group by Protein) a
  JOIN (SELECT Protein, count(mirna) mirnacount from exp3 group by Protein) b
    ON a.Protein = b.Protein AND a.mirnacount = b.mirnacount

You might consider switching to PostgreSQL if a full complement of set operations are needed for your project.

score 0 · Answer 2 · answered Dec 01 '16 at 08:42

SELECT T1.Protein,count(T1.miRNAID) FROM exp2 AS T1 INNER JOIN exp3 AS T2 ON T1.Protein = T2.Protein AND T1.Target_Protein_id= T2.Target_Protein_id AND T1.miRNAID=T2.miRNAID
GROUP BY T1.Protein ORDER BY count(T1.miRNAID)

this works perfectly for me.

Intersect command is not working

2 Answers2