Advertisement
Guest User

Untitled

a guest
Sep 5th, 2015
121
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.10 KB | None | 0 0
  1. SELECT films.*, GROUP_CONCAT(tags.tag) AS genre
  2. FROM films
  3. INNER JOIN tags_to_film ON films.id = tags_to_film.film_id
  4. LEFT JOIN tags ON tags_to_film.tag_id = tags.id
  5. GROUP BY films.title
  6.  
  7. SELECT films.*, GROUP_CONCAT(tags.tag) AS genre
  8. FROM films
  9. INNER JOIN tags_to_film ON films.id = tags_to_film.film_id
  10. LEFT JOIN tags ON tags_to_film.tag_id = tags.id
  11. WHERE tags.tag='$tag'
  12. GROUP BY films.title
  13.  
  14. create table f (id int, n text);
  15.  
  16. insert into f values
  17. (1, 'slq-запросы')
  18. ,(2, 'программирование')
  19. ,(3, 'субд')
  20. ;
  21.  
  22. create table t (id int, n text);
  23. insert into t values
  24. (1, 'ужас')
  25. ,(2, 'кошмар')
  26. ,(3, 'жуть')
  27. ;
  28.  
  29. create table ft (f int, t int);
  30. insert into ft values
  31. (1, 1)
  32. ,(1, 2)
  33. ,(1, 3)
  34. ,(2, 1)
  35. ,(2, 3)
  36. ,(3, 2)
  37. ;
  38.  
  39. select f.*, t.n
  40. from f
  41. join ft
  42. on ft.f = f.id
  43. join t
  44. on t.id = ft.t
  45.  
  46. | id | n | n |
  47. |----|------------------|--------|
  48. | 1 | slq-запросы | ужас |
  49. | 1 | slq-запросы | кошмар |
  50. | 1 | slq-запросы | жуть |
  51. | 2 | программирование | ужас |
  52. | 2 | программирование | жуть |
  53. | 3 | субд | кошмар |
  54.  
  55. select f.*, group_concat(t.n)
  56. from f
  57. join ft
  58. on ft.f = f.id
  59. join t
  60. on t.id = ft.t
  61. group by f.id
  62.  
  63. | id | n | group_concat(t.n) |
  64. |----|------------------|-------------------|
  65. | 1 | slq-запросы | ужас,кошмар,жуть |
  66. | 2 | программирование | ужас,жуть |
  67. | 3 | субд | кошмар |
  68.  
  69. select f.*, group_concat(t1.n)
  70. from f
  71. join ft ft1
  72. on ft1.f = f.id
  73. join t t1
  74. on t1.id = ft1.t
  75. join ft ft2
  76. on ft2.f = f.id
  77. join t t2
  78. on t2.id = ft2.t and t2.n = 'ужас'
  79. group by f.id
  80.  
  81. | id | n | group_concat(t1.n) |
  82. |----|------------------|--------------------|
  83. | 1 | slq-запросы | ужас,кошмар,жуть |
  84. | 2 | программирование | ужас,жуть |
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement