code_junkie

What is the best way to represent a many-to-many relationship between records in a single SQL table

Nov 13th, 2011
187
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.02 KB | None | 0 0
  1. entities
  2. id name
  3. 1 Apple
  4. 2 Orange
  5. 3 Banana
  6. 4 Carrot
  7. 5 Mushroom
  8.  
  9. entity_entity
  10. entity_id_a entity_id_b
  11. 1 2
  12. 5 1
  13. 4 1
  14. 5 4
  15. 1 3
  16.  
  17. CREATE UNIQUE INDEX ix_a_b ON entity_entity(entity_id_a, entity_id_b);
  18. CREATE INDEX ix_b ON entity_entity(entity_id_b);
  19.  
  20. INSERT
  21. INTO entity_entity (entity_id_a, entity_id_b)
  22. VALUES (LEAST(@id1, @id2), GREATEST(@id1, @id2))
  23.  
  24. SELECT entity_id_b
  25. FROM entity_entity
  26. WHERE entity_id_a = @id
  27. UNION ALL
  28. SELECT entity_id_a
  29. FROM entity_entity
  30. WHERE entity_id_b = @id
  31.  
  32. SELECT related.* FROM entities AS search
  33. LEFT JOIN entity_entity map ON map.entity_id_a = search.id
  34. LEFT JOIN entities AS related ON map.entity_id_b = related.id
  35. WHERE search.name = 'Search term'
  36.  
  37. CREATE TABLE equivalence_class (
  38. class_id int -- surrogate, IDENTITY, autonumber, etc.
  39. ,entity_id int
  40. )
  41.  
  42. SELECT c2.entity_id
  43. FROM equivalence_class c1
  44. INNER JOIN equivalence_class c2
  45. ON c1.entity_id = @entity_id
  46. AND c1.class_id = c2.class_id
  47. AND c2.entity_id <> @entity_id
  48.  
  49. SELECT c2.entity_id
  50. FROM equivalence_class c1
  51. INNER JOIN equivalence_class c2
  52. ON c1.entity_id = @entity_id
  53. AND c1.class_id = c2.class_id
  54. AND c2.entity_id <> c1.entity_id
  55.  
  56. SELECT DISTINCT
  57. CASE
  58. WHEN entity_id_a <> @entity_id THEN entity_id_a
  59. WHEN entity_id_b <> @entity_id THEN entity_id_b
  60. END AS equivalent_entity
  61. FROM entity_entity
  62. WHERE entity_id_a = @entity_id OR entity_id_b = @entity_id
  63.  
  64. SELECT entity_id_b AS equivalent_entity
  65. FROM entity_entity
  66. WHERE entity_id_a = @entity_id
  67. UNION
  68. SELECT entity_id_a AS equivalent_entity
  69. FROM entity_entity
  70. WHERE entity_id_b = @entity_id
  71.  
  72. select * from entities
  73. where entity_id in
  74. (
  75. select entity_id_b
  76. from entity_entity
  77. where entity_id_a = @lookup_value
  78. )
  79.  
  80. select if(entity_id_a=:entity_id,entity_id_b,entity_id_a) as related_entity_id where :entity_id in (entity_id_a, entity_id_b)
Add Comment
Please, Sign In to add comment