MySQL Multiple Joins from 3 Tables

Question

I need to select all records from assets_tbl (A). Included with these records I need to have any lightboxes (lightbox_name) that are linked to the asset_id from assets_tbl where author = "scott@co.com" OR authorized_viewers includes "scott@co.com"

I think this is close to what I need but it returns multiple rows of the same record:

SELECT
  A.*,
  C.lightbox_name,
  C.author,
  C.authorized_viewers
FROM
  media_tbl A
  LEFT JOIN lightbox_assets_tbl B ON A.asset_id = B.asset_id
  LEFT JOIN lightboxes_tbl C 
    ON B.lightbox_id = C.id
    AND C.author = "scott@co.com"
  LEFT JOIN lightboxes_tbl D ON D.authorized_viewers LIKE "scott@co.com"
ORDER BY A.id DESC

Here are the tables:

lightboxes_tbl
+-----+----------------+---------------+---------------------+
|id   |lightbox_name   |author         |authoried_viewers    |
+-----+----------------+---------------+---------------------+
|100  | aircraft-types |scott@co.com   |jon@co.com,aj@co.com |
|101  | maintenance    |nicole@co.com  |jon@co.com           |
|102  | ramp           |nicole@co.com  |scott@co.com         |
+-----+----------------+---------------+---------------------+



lightbox_assets_tbl
+-----+-------------+-------------+---------------+----------+
|id   |lightbox_id  |asset_name   |asset_path     | asset_id |
+-----+-------------+-------------+---------------+----------+
|1    |100          |a321.jpg     |project1/imgs/ | 3700     |
|2    |100          |b757.jpg     |project1/imgs/ | 3444     |
|3    |101          |FlyBy.swf    |project4/imgs/ | 1444     |
|4    |102          |Door_757.swf |project5/imgs/ | 3701     |
+-----+-------------+-------------+---------------+----------+


assets_tbl
+-----+---------------------+-------------------------------------+
|asset_id   |asset_name           | asset_location                |
+-----------+---------------------+-------------------------------+
|3700       |a321.jpg             |Libraries\Library_Media\Images |
|200        |757_Taxi.swf         |Libraries\Library_Media\Images |
|3444       |b757.jpg             |Libraries\Library_Media\Images |
|1444       |FlyBy.swf            |Libraries\Library_Media\Images |
|3701       |Door_757.swf         |Libraries\Library_Media\Images |
+----------+---------------------+--------------------------------+

Here are the expected RESULTS of the query:

+-----------+---------------------+-------------------------------+------------------+-------------+------------------------+
|asset_id   |asset_name           | asset_location                |lightbox_name     | author      | authorized_viewers     |
+-----------+---------------------+-------------------------------+------------------+-------------+------------------------+
|3700       |a321.jpg             |Libraries\Library_Media\Images |aircraft-types    |scott@co.com |jon@co.com,aj@co.com    |
+-----------+---------------------+-------------------------------+------------------+-------------+------------------------+
|200        |757_Taxi.swf         |Libraries\Library_Media\Images |NULL              |NULL         |NULL                    |
+-----------+---------------------+-------------------------------+------------------+-------------+------------------------+
|3444       |b757.jpg             |Libraries\Library_Media\Images |aircraft-types    |scott@co.com |jon@co.com,aj@co.com    |
+-----------+---------------------+-------------------------------+------------------+-------------+------------------------+
|1444       |FlyBy.swf            |Libraries\Library_Media\Images |NULL              |NULL         |NULL                    |
+-----------+---------------------+-------------------------------+------------------+-------------+------------------------+
|3701       |Door_757.swf         |Libraries\Library_Media\Images |ramp              |nicole@co.com|scott@co.com            |
+----------+---------------------+--------------------------------+------------------+-------------+------------------------+

---

SQL Fiddle

Thanks!

Answer 1

I'm wondering why you need two joins to the lightboxes_tbl table. It seems like the second reference to that table (alias D) is unnecessary. Seems like you could just use an OR.

As a demonstration, replicating the predicates in your query:

LEFT JOIN lightboxes_tbl C
       ON B.lightbox_id = C.id
      AND ( C.author = 'scott@co.com'
          OR C.authorized_viewers = 'scott@co.com'
          )

But given that authorized_user contains a comma separated list (ACCKKK!!!), I suspect you really want to look for an exact match to an item in the comma separated list. The LIKE comparison that currently have is equivalent to an equals comparison (to the entire contents of authorized_viewers column). You could add '%' wildcard characters to search for the value as part of the string...

But that approach is will also match strings containing e.g. ebscott@co.com, which may not be what you really want.

You could use the FIND_IN_SET function to find an exact match within the comma separated list...

LEFT JOIN lightboxes_tbl C
       ON B.lightbox_id = C.id
      AND ( C.author = 'scott@co.com'
          OR FIND_IN_SET('scott@co.com',C.authorized_viewers)
          )

---

Storing comma separated lists is a SQL anti-pattern. I recommend Bill Karwin's book: SQL Anti-Patterns: Avoiding the Pitfalls of Database Programming

http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557