Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT films.*, GROUP_CONCAT(tags.tag) AS genre
- FROM films
- INNER JOIN tags_to_film ON films.id = tags_to_film.film_id
- LEFT JOIN tags ON tags_to_film.tag_id = tags.id
- GROUP BY films.title
- SELECT films.*, GROUP_CONCAT(tags.tag) AS genre
- FROM films
- INNER JOIN tags_to_film ON films.id = tags_to_film.film_id
- LEFT JOIN tags ON tags_to_film.tag_id = tags.id
- WHERE tags.tag='$tag'
- GROUP BY films.title
- create table f (id int, n text);
- insert into f values
- (1, 'slq-запросы')
- ,(2, 'программирование')
- ,(3, 'субд')
- ;
- create table t (id int, n text);
- insert into t values
- (1, 'ужас')
- ,(2, 'кошмар')
- ,(3, 'жуть')
- ;
- create table ft (f int, t int);
- insert into ft values
- (1, 1)
- ,(1, 2)
- ,(1, 3)
- ,(2, 1)
- ,(2, 3)
- ,(3, 2)
- ;
- select f.*, t.n
- from f
- join ft
- on ft.f = f.id
- join t
- on t.id = ft.t
- | id | n | n |
- |----|------------------|--------|
- | 1 | slq-запросы | ужас |
- | 1 | slq-запросы | кошмар |
- | 1 | slq-запросы | жуть |
- | 2 | программирование | ужас |
- | 2 | программирование | жуть |
- | 3 | субд | кошмар |
- select f.*, group_concat(t.n)
- from f
- join ft
- on ft.f = f.id
- join t
- on t.id = ft.t
- group by f.id
- | id | n | group_concat(t.n) |
- |----|------------------|-------------------|
- | 1 | slq-запросы | ужас,кошмар,жуть |
- | 2 | программирование | ужас,жуть |
- | 3 | субд | кошмар |
- select f.*, group_concat(t1.n)
- from f
- join ft ft1
- on ft1.f = f.id
- join t t1
- on t1.id = ft1.t
- join ft ft2
- on ft2.f = f.id
- join t t2
- on t2.id = ft2.t and t2.n = 'ужас'
- group by f.id
- | id | n | group_concat(t1.n) |
- |----|------------------|--------------------|
- | 1 | slq-запросы | ужас,кошмар,жуть |
- | 2 | программирование | ужас,жуть |
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement