MySQL Get Matching Results From two queries?

Question

I have some php code that generates multiple MySql queries and combines the results. I am trying to find a way to combine the queries so the only fields that match in both queries are outputed.

Example of PHP Codes Output.

SELECT LoadData_3 pow FROM tblLoadData WHERE LoadData_1 = 'test1' AND LoadData_2 = 'test2' 
UNION ALL
SELECT LoadData_3 pow FROM tblLoadData WHERE LoadData_1 = 'test3' AND LoadData_2 = 'test4'

I want only the LoadData_3 fields that are the same in both queries.

Mike Dinescu · Accepted Answer · 2012-02-10T22:16:18.057

9

Use the INTERSECT operation:

(SELECT LoadData_3 pow 
  FROM tblLoadData 
 WHERE LoadData_1 = 'test1' 
   AND LoadData_2 = 'test2')
INTERSECT                     -- return the intersection of the two queries
(SELECT LoadData_3 pow 
  FROM tblLoadData 
 WHERE LoadData_1 = 'test3' 
   AND LoadData_2 = 'test4')

EDIT

For MySQL you can use INNER JOIN USING (..)

SELECT pow -- Use DISTINCT pow instead if you want unique values (like INTERSECT)
  FROM (SELECT LoadData_3 pow 
          FROM tblLoadData 
         WHERE LoadData_1 = 'test1' 
           AND LoadData_2 = 'test2') T1
  INNER JOIN
       (SELECT LoadData_3 pow 
          FROM tblLoadData 
         WHERE LoadData_1 = 'test3' 
           AND LoadData_2 = 'test4') T2
  USING (pow)

Or using a WHERE IN clause:

SELECT LoadData_3 pow -- Use DISTINCT pow instead if you want unique values (like INTERSECT)
  FROM tblLoadData 
 WHERE LoadData_1 = 'test1' 
   AND LoadData_2 = 'test2'
   AND LoadData_3 IN (SELECT LoadData_3 pow 
                        FROM tblLoadData 
                       WHERE LoadData_1 = 'test3' 
                         AND LoadData_2 = 'test4')

edited Feb 10 '12 at 22:16

answered Feb 10 '12 at 21:48

Mike Dinescu

54,171
16
118
151

Ok when I try doing an intersect I receive an error. Is this something to do with my code? SELECT LoadData_Powder pow FROM tblLoadData WHERE LoadData_Case = '308 Winchester' AND LoadData_Bullet = '#3039 FTX' INTERSECT SELECT LoadData_Powder pow FROM tblLoadData WHERE LoadData_Case = '30-30 Winchester' AND LoadData_Bullet = '#3039 FTX' – Talon06 Feb 10 '12 at 21:58
#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 'INTERSECT SELECT LoadData_Powder FROM tblLoadData WHERE LoadData_Case = '30-30 W' at line 2 – Talon06 Feb 10 '12 at 22:02
I believe intersect may be SQL only – Talon06 Feb 10 '12 at 22:12
MySQL doesn't have `INTERSECT` – Esailija Feb 10 '12 at 22:13
1

@Talon06, yeah. It appears that way. Anyway, for MySQL you can use INNER JOIN USING(..) or do a SELECT FROM and use a WHERE IN clause to filter (See my edited response) – Mike Dinescu Feb 10 '12 at 22:17
Thanks for posting the Inner Join Example that got me on my way. – Talon06 Feb 10 '12 at 22:28

score 2 · Answer 2 · edited May 23 '17 at 10:28

2

Big Edit!

I've spent too much time in Oracle, and I skimmed over that article too quickly! The above answer was edited before mine. You can also take a look at the following question:

Alternative to Intersect in MySQL

edited May 23 '17 at 10:28

Community

1
1

answered Feb 10 '12 at 21:49

Dan

5,081
1
18
28

The article describes an alternative to MySQL (right below your code it says `can be rewritten as...`) as MySQL doesn't support `INTERSECT` – Esailija Feb 10 '12 at 22:12

MySQL Get Matching Results From two queries?

2 Answers2