cssBlaster21895 cssBlaster21895 - 4 months ago 12
MySQL Question

Dsictint bi-directional relation

I have table that describes realtionships between other tables in database. Every user can have any file, and every file can have any user.

If I get one files' relations and I don't have this file relation to user, but user has relation to that file. I want to see that.

When both have relations to themselves I don't want to see twice records.

As an input I have record type and record id. How to achieve that?

Closure table:

+--------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| record_id | int(11) | NO | | NULL | |
| record_type | varchar(200) | NO | | NULL | |
| second_record_id | int(11) | NO | | NULL | |
| second_record_type | varchar(200) | NO | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+


Sample data:

+----+-----------+-------------+------------------+--------------------+
| id | record_id | record_type | second_record_id | second_record_type |
+----+-----------+-------------+------------------+--------------------+
| 1 | 1 | files | 1 | users |
| 2 | 2 | users | 1 | files |
| 3 | 3 | users | 1 | files |
| 4 | 2 | files | 1 | users |
| 5 | 1 | users | 1 | files |
| 6 | 1 | files | 3 | users |
+----+-----------+-------------+------------------+--------------------+


I've tried

SELECT * FROM closure
WHERE record_id=1 OR second_record_id = 1
AND record_type="files" OR second_record_type="files"
GROUP BY "files"
HAVING record_id=1 OR second_record_id=1


but it gets me one relation:

+----+-----------+-------------+------------------+--------------------+
| id | record_id | record_type | second_record_id | second_record_type |
+----+-----------+-------------+------------------+--------------------+
| 1 | 1 | files | 1 | users |
+----+-----------+-------------+------------------+--------------------+


My desired result would be:

+----+-----------+-------------+------------------+--------------------+
| id | record_id | record_type | second_record_id | second_record_type |
+----+-----------+-------------+------------------+--------------------+
| 1 | 1 | files | 1 | users |
| 2 | 2 | users | 1 | files |
| 6 | 1 | files | 3 | users |
+----+-----------+-------------+------------------+--------------------+


Sql fiddle




EDIT

I've dropped id column finally.

Answer

I think you just want to compare both the "1" and the "files" at the same type, for each record type. This should produce your desired result:

SELECT c.*
FROM closure c
WHERE (record_id = 1 and record_type = 'files') OR
      (second_record_id = 1 and second_record_type = 'files');
Comments