2

I'd like some assistance in fixing this query. Let me explain the background on what it currently does, and then what I need to integrate.

First, here are my current tables.

users (performers) table

the users table lists all users. we are going to assume for this query that all users selected are type performers.

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

here is a dump of the users table:

id  hash                              first_name  last_name  
 1  092ee63c6698851ba72183388bff1b3b  Jonathan    Kushner    
 2  2d234affe1d08c3394cd499f297d7380  Dustin      Kushner   

aliases table

the aliases table lists all aliases. we connect this table to performers in a further table.

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

here is a dump of the 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 to aliases in the aliases table.

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

here's a dump of the 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 all videos.

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

here's a dump of the videos table:

id  hash                             title       
 1  89efaed413163be4557133266ce295b4 crazy sports blooper

videos_performers

the videos performers table connects videos to a user of type performer.

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

heres a dump of the videos performers table:

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

OK. Now let me show you my current query:

    SELECT        
      `users`.`first_name`,
      `users`.`last_name`,
      `aliases`.`title` AS `alias`,
      `videos`.`title` AS `video`
    FROM
      `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`  
    WHERE (
        `videos`.`hash` = '89efaed413163be4557133266ce295b4'
      ) 
    ORDER BY `users`.`last_name` ASC

This query does the following:

  • selects users who are performers for a video. we need to include the aliases tables to grab the performers aliases. (note: performers can have multiple aliases)

Here's a 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 you can see, this video contains 2 performers - Jonathan Kushner and Dustin Kushner. However, Dustin has 2 aliases so it has 2 records for him

What I'd like to do is incorporate having video performers connected via aliases to videos, so instead of having 2 records for Dustin since he has 2 aliases, it would only connect to the alias being used for that specific video.

For example, the output for this video would be:

first_name  last_name  alias        video
Jonathan    Kushner    Greatness    crazy sports blooper
Dustin      Kushner    Mr.Darkness  crazy sports blooper

This resultset shows only 2 records now, one for Dustin as Mr.Darkness and one for Jonathan as Greatness. You'll note that no longer there exists a record for Dustin as Mr.Shadow since Mr.Shadow was not used as the alias for that video.

Here's my current work as to how to solve this. I've created a videos_performers_aliases table that connects videos to performers to aliases.

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

And then I would modify the query from above as follows:

    SELECT        
      `users`.`first_name`,
      `users`.`last_name`,
      `aliases`.`title` AS `alias`,
      `videos`.`title` AS `video`
    FROM
      `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`
    WHERE (
        `videos`.`hash` = '89efaed413163be4557133266ce295b4'
      ) 
    ORDER BY `users`.`last_name` ASC

As you can see, i added this to the query:

LEFT JOIN `videos_performers_aliases`
        ON `videos_performers_aliases`.`video_id` = `videos`.`id`

I'd like my output to be the following:

first_name  last_name  alias        video
Jonathan    Kushner    Greatness    crazy sports blooper
Dustin      Kushner    Mr.Darkness  crazy sports blooper

This insinuates that we have one performer as Mr.Darkness and one performer as Greatness. As you can see, Mr.Shadow is not in the resultset.

Unfortunately, the resultset here is 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 is as far as I've gotten on it.

Any help would be appreciated.

  • First, which one of the aliases do you want to show, lastone? firstone?. Second, this is a really easy query fix, but, you need to clear the first step here. – oNare Aug 30 '15 at 0:10
  • @oNare I updated my question. Check directly underneath my comment about adding a left join for my new table. – jkushner Aug 30 '15 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.