How to get data by matching same table in Mysql

Question

I have following table structure in Mysql DB Table: Mytable

--------------------------------
| uid | locationid | projectid |
--------------------------------
|  2  |      4     |    2     | 
|  2  |      4     |    2     | 
|  2  |      3     |    5     | 
|  2  |      3     |    5     | 
|  2  |      1     |    2     | 
|  2  |      1     |    2     | 
|  2  |      1     |    2     | 
|  2  |      1     |    1     | 
|  3  |      1     |    5     | 
|  3  |      1     |    2     | 
|  3  |      1     |    1     | 
|  3  |      1     |    1     | 
|  7  |      3     |    1     | 
|  5  |      1     |    5     | 
|  6  |      4     |    2     | 
|  5  |      1     |    5     | 
|  3  |      4     |    1     | 
|  3  |      3     |    3     | 
|  7  |      4     |    2     | 
|  7  |      4     |    3     | 

Say I passed uid=2 then for uid=2 unique combination of locationid and projectid is this

|  2  |      4     |    2     | 
|  2  |      3     |    5     | 
|  2  |      1     |    2     | 
|  2  |      1     |    1     | 

Now I want all uids with above locationid and projectid combination match. Means result should be

|  3  |      1     |    2     | 
|  3  |      1     |    1     | 
|  3  |      1     |    1     | 
|  6  |      4     |    2     | 
|  7  |      4     |    2     | 

If I pass uid=3 then result should be

|  2  |      1     |    2     | 
|  2  |      1     |    2     | 
|  2  |      1     |    2     | 
|  2  |      1     |    1     | 
|  5  |      1     |    5     | 
|  5  |      1     |    5     | 

For this I used following query but it gives wrong result as locationid and projectid combination is not match properly

 SELECT a.*
 FROM Mytable a, Mytable b 
 WHERE a.locationid = b.locationid
 AND a.projectid = b.projectid
 AND a.locationid IN (SELECT DISTINCT locationid FROM Mytable WHERE uid=$pmid)
 AND a.projectid IN(SELECT DISTINCT projectid FROM Mytable WHERE uid=$pmid)
 AND a.uid !=$pmid

Here $pmid is uid value which I passed. What is wrong with my query? Is my query is right? Please help me.

Thanks in advance.

Answer 1

try this:

SELECT t.* 
FROM   Mytable t
JOIN
       ( SELECT distinct locationid , projectid
         FROM Mytable
         WHERE uid=<uid>)a
ON     t.locationid =a.locationid 
AND    t.projectid=a.projectid
WHERE  t.uid != <uid>

Answer 2

Try this query -

SELECT t1.* FROM mytable t1
  JOIN (SELECT * FROM mytable WHERE uid = 2 GROUP BY locationid, projectid) t2
    ON t1.uid <> t2.uid AND
       t1.locationid = t2.locationid AND
       t1.projectid = t2.projectid;

Specify your uid in WHERE clause (second line).

Answer 3

This should work:

SELECT a.*
FROM Mytable a
     INNER JOIN (SELECT DISTINCT locationid, projectid
                 FROM   Mytable
                 WHERE  uid = 2
                ) b
        ON a.locationid = b.locationid
           AND a.projectid = b.projectid
           AND a.uid <> 2;

Example: SQLFiddle