mysql - Join integration query involving multiple associations -

i posted on dba not getting responses added here.

here sqlfiddle

i'd assistance in fixing query. problem simple i'm not able solve it. let me explain background on does, , need integrate.

first, here current tables.

users (performers) table

the users table lists users. going assume query users selected type performers.

users ----------- id hash first_name last_name 

here dump of users table:

id  hash                              first_name  last_name    1  092ee63c6698851ba72183388bff1b3b  jonathan    kushner      2  2d234affe1d08c3394cd499f297d7380  dustin      kushner    

aliases table

the aliases table lists aliases. connect table performers in further table.

aliases ---------- id hash title 

here dump of aliases table:

id  hash             title         1  436etgfgdggdd    greatness  2  54645655gggdgdg  mr.darkness  3  ffsafsdsfdfdssf  mr.shadow 

performers_aliases table

the performers_aliases table connects users of type performers aliases in aliases table.

performers_aliases ---------- id hash performer_id (connects users.id column) alias_id 

here's dump of performers_aliases table:

id  hash             performer_id  alias_id    1  dsaadsasdadsads             1           1  2  d4544534553                 2           2  3  adfasdadsadsa               2           3 

videos table

the videos table lists videos.

videos ---------- id hash title 

here's dump of videos table:

id  hash                             title         1  89efaed413163be4557133266ce295b4 crazy sports blooper 

videos_performers

the videos performers table connects videos user of type performer.

videos_performers ---------- id hash video_id performer_id (connects users.id column) 

heres dump of videos performers table:

id  hash                              video_id  performer_id    1  3a7e0f543af5152c623ad8156079bb73         1               1  2  c0abf5f50f6382c744a9359d7dbc4c8c         1               2 

ok. let me show current query:

    select               `users`.`first_name`,       `users`.`last_name`,       `aliases`.`title` `alias`,       `videos`.`title` `video`           `users`        left join `performers_aliases`          on `performers_aliases`.`performer_id` = `users`.`id`        left join `aliases`          on `aliases`.`id` = `performers_aliases`.`alias_id`        left join `videos_performers`          on `videos_performers`.`performer_id` = `users`.`id`        left join `videos`          on `videos`.`id` = `videos_performers`.`video_id`       (         `videos`.`hash` = '89efaed413163be4557133266ce295b4'       )      order `users`.`last_name` asc 

this query following:

• selects users performers video. need include aliases tables grab performers aliases. (note: performers can have multiple aliases)

here's resultset:

first_name  last_name  alias        video jonathan    kushner    greatness    crazy sports blooper dustin      kushner    mr.darkness  crazy sports blooper dustin      kushner    mr.shadow    crazy sports blooper 

as can see, video contains 2 performers - jonathan kushner , dustin kushner. however, dustin has 2 aliases has 2 records him

what i'd incorporate having video performers connected via aliases videos, instead of having 2 records dustin since has 2 aliases, connect alias being used specific video.

for example, output video be:

first_name  last_name  alias        video jonathan    kushner    greatness    crazy sports blooper dustin      kushner    mr.darkness  crazy sports blooper 

this resultset shows 2 records now, 1 dustin mr.darkness , 1 jonathan greatness. you'll note no longer there exists record dustin mr.shadow since mr.shadow not used alias video.

here's current work how think solving this. i've created videos_performers_aliases table connects videos performers aliases. don't think need performer_id column id anyway. believe important video alias connection.

videos_performers_aliases -------------------- id hash video_id performer_id alias_id 

here dump of videos_performers_aliases table:

    id  hash                   video_id  performer_id  alias_id               1  fdsa97asd987das7das97         1             1         1        2  dfdfsdfsfdsdfsdfsfd           1             2         2 

and modify query above follows:

    select               `users`.`first_name`,       `users`.`last_name`,       `aliases`.`title` `alias`,       `videos`.`title` `video`           `users`        left join `performers_aliases`          on `performers_aliases`.`performer_id` = `users`.`id`        left join `aliases`          on `aliases`.`id` = `performers_aliases`.`alias_id`        left join `videos_performers`          on `videos_performers`.`performer_id` = `users`.`id`        left join `videos`          on `videos`.`id` = `videos_performers`.`video_id`       left join `videos_performers_aliases`         on `videos_performers_aliases`.`video_id` = `videos`.`id`     (         `videos`.`hash` = '89efaed413163be4557133266ce295b4'       )      order `users`.`last_name` asc 

as can see, added query:

left join `videos_performers_aliases`         on `videos_performers_aliases`.`video_id` = `videos`.`id` 

i'd output following:

first_name  last_name  alias        video jonathan    kushner    greatness    crazy sports blooper dustin      kushner    mr.darkness  crazy sports blooper 

this insinuates have 1 performer mr.darkness , 1 performer greatness. can see, mr.shadow not in resultset.

unfortunately, resultset here still:

first_name  last_name  alias        video jonathan    kushner    greatness    crazy sports blooper dustin      kushner    mr.darkness  crazy sports blooper dustin      kushner    mr.shadow    crazy sports blooper 

this far i've gotten on it.

any appreciated.

the missing piece in way had expressed query condition in videos_perfomers_aliases join, join both videos.id , aliases.id tables.

left join `videos_performers_aliases`   on `videos_performers_aliases`.`video_id` = `videos`.`id`   , `videos_performers_aliases`.`alias_id` = `aliases`.id` 

however, using left join there cause both aliases returned. instead using inner join return connected alias.

inner join `videos_performers_aliases`   on `videos_performers_aliases`.`video_id` = `videos`.`id`   , `videos_performers_aliases`.`alias_id` = `aliases`.id` 

here's version modified produce intended result: http://sqlfiddle.com/#!9/ec26c/21

better, fewer joins:

but entire query can simplified. because videos_performers_aliases includes both video , alias information already, renders videos_performers table unnecessary query (and maybe others). can execute entire query chain:

users(id) --> (performer_id)performers_aliases(alias_id) --> (id)aliases(id) --> (alias_id)videos_performers_aliases(video_id) --> (id)videos 

it therefore possible eliminate 1 of joins, , simplify join condition videos_performers_aliases require 1 part.

select   `users`.`first_name`,   `users`.`last_name`,   `aliases`.`title` alias,   `videos`.`title` video   `users`    left join `performers_aliases`      on `performers_aliases`.`performer_id` = `users`.`id`    left join `aliases`      on `aliases`.`id` = `performers_aliases`.`alias_id`    left join `videos_performers_aliases`     on videos_performers_aliases.alias_id = aliases.id   left join `videos`     on `videos`.id = videos_performers_aliases.video_id (   `videos`.`hash` = '89efaed413163be4557133266ce295b4' )  order `users`.`last_name` asc 

here in action, producing expected result: http://sqlfiddle.com/#!9/ec26c/19

about videos_performers_aliases, said:

i don't think need performer_id column id anyway. believe important video alias connection.

that assessment correct. connection needed between videos , aliases because can join users via performers_aliases. isn't harmful keep it, except if reason must reassign alias, means updating performer_id in more 1 related table. keep in mind -- suspect you've considered though.