#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    31
    Rep Power
    6

    Table joining, replace IDs with values


    Generally, I always have three tables for joining, but in this situation, I required to have only two tables and this has caused a bit of a snag.

    What I currently have is a table for a single record. this record has multiple files of which each column has different IDs referencing a table with those IDs. As an example the tables look like the following:

    Code:
    Table - PROJECT
    --------------------------------------------------------------------
    |   id   |  name  |  render_1  |  render_2  |  render_3  |  .....  |
    --------------------------------------------------------------------
    |   1    |  name  |     1      |     2      |     3      |  .....  |
    --------------------------------------------------------------------
    
    Table - IMAGES
    -------------------
    |   id   |  path  |
    -------------------
    |   1    |  url   |
    -------------------
    |   2    |  url   |
    -------------------
    |   3    |  url   |
    -------------------
    What I want to try and achieve is the following:

    Code:
    --------------------------------------------------------------------
    |   id   |  name  |  render_1  |  render_2  |  render_3  |  .....  |
    --------------------------------------------------------------------
    |   1    |  name  |    path1   |    path2   |    path3   |  .....  |
    --------------------------------------------------------------------
    In my current situation (API restrictions), I'm unable to use a link table so I'm forced to do things this way. Is this actually possible or would I have to do an SQL statement each time I want the "path" for each individual image?

    The only way I can think of doing this is this way:

    Code:
    SELECT project.render_1, project.render_2, project.render_3, images.path
    FROM art
    INNER JOIN files
    	ON images.id = project.render_1
    	OR images.id = project.render_2
    	OR images.id = project.render_3
    WHERE project.id = 1
    This means that I would have to do a double loop which I want to try and avoid as I would end up with the following:

    Code:
    --------------------------------------------------------------------
    |   id   |  name  |  render_1  |  render_2  |  render_3  |  path   |
    --------------------------------------------------------------------
    |   1    |  name  |     1      |      2     |     3      |  path1  |
    |   1    |  name  |     1      |      2     |     3      |  path2  |
    |   1    |  name  |     1      |      2     |     3      |  path3  |
    --------------------------------------------------------------------
    This looks rather messy, surely there's a better way around this?

    Edit: I think I've figured it out:

    Code:
    SELECT project.id, project.name, a.path AS render_1, b.path AS render_2, c.path AS render_3
    FROM project
    INNER JOIN images a ON a.id = project.render_1
    INNER JOIN images b ON b.id = project.render_2
    INNER JOIN images c ON c.id = project.render_3
    INNER JOIN .......
    WHERE project.id = 1
    Perhaps there's another way around this? Or is this the only way?
    Last edited by Varsh; April 19th, 2017 at 05:48 PM.
  2. #2
  3. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,118
    Rep Power
    2010
    Maybe you can try explain what you want to accomplish with the end result?
    Why can't you use a standard join at several lines?
    Have you looked at GROUP_CONCAT, maybe that can work too?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,623
    Rep Power
    1811
    Is that series of render_? columns you want to end up with of arbitrary length or fixed? In any event I would have expected a link table between the project and image tables and a sprinkling of normalisation performed!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo