0

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.

cssBlaster21895
  • 3,670
  • 20
  • 33
  • I can't see the desired result – Strawberry Jul 10 '16 at 10:35
  • I've just put what I have researched for now, maybe its too much information, should I delete the "research part"? – cssBlaster21895 Jul 10 '16 at 10:46
  • You should add the result you want obtain , based on the sample proivided – ScaisEdge Jul 10 '16 at 11:04
  • 1
    This question is written very poorly, Create two tables for the many-to-many and a [Junction Table](http://stackoverflow.com/a/32620163) as I see it. – Drew Jul 10 '16 at 12:48
  • I know, but sometimes its hard to explain owns problem. The names are poor for sure, when I look at them - I don't know what is what at the first sight. I've read your answer. Thank you. – cssBlaster21895 Jul 10 '16 at 14:14

2 Answers2

1

You don't have aggregation function so the group by and the having clause are unuseful if you wondt distinct add distinct clause (but the use of id don't permit a proper work)

SELECT  * 
FROM closure 
WHERE ( record_id=1 OR second_record_id = 1 ) 
AND  (record_type="files" OR second_record_type="files" )

or 

SELECT distinct record_id, record_type, second_record_id, second_record_type 
FROM closure 
WHERE ( record_id=1 OR second_record_id = 1 )
AND ( record_type="files" OR second_record_type="files" )
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • It shows me all records, unfortunately. I want to see where the record id 1 and type "files" is connected. But also don\t show same relation twice. – cssBlaster21895 Jul 10 '16 at 11:14
  • It shows users 1 for files 2, thats not desired and it doesnt distinct relations users 3 files 1 – cssBlaster21895 Jul 10 '16 at 11:32
  • based on your sample both the query should show also the 3th rows .. try post a sqlfiddle so we can check .. the behavior of the query .. – ScaisEdge Jul 10 '16 at 11:36
  • I've even tried to build some kind of inner join, but even couldnt make it work http://sqlfiddle.com/#!9/665af6/13 – cssBlaster21895 Jul 10 '16 at 13:54
1

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');
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786