How do I join multiple tables to fetch the results based on a given criteria?

Question

I've been looking at this far too long and my brain feels like spaghetti noodles. Could someone help me out with a couple of queries?

Tables:

Presentation
------------
id          int 
name        varchar 
fk_template int     (references template.id)

Template
--------
id          int 
name        varchar 

Position
--------
id          int 
zorder      int     (if it is the 1st, 2nd, 3rd position of the given template)
fk_template int     (references Template.id)

Asset
-----
id          int 
name        varchar 
description varchar 

AssetForPresentation
--------------------
fk_asset        int  (references Asset.id)
fk_presentation int  (references Presentation.id)
fk_position     int  (references Position.id)

What I think I need to be asking at this point is basically "give me all of the assets AND their positions for this template."

You see, when a Presentation is brought it, it has a specific Template. Each Template has designated Positions, and each Position holds an Asset or NULL.

I need to be able to bring in all Assets and their individual positions for a particular Template used by a Presentation. How would I query for something like that?

I hope this makes sense to you.

Answer 1

Based on what you have described in your question, you need to get all assets for a given template. I have created the table structure but didn't define the relation constraints on the table but used them while formulating the query.

You can join the Asset table to the AssetForPresentation table. Through AssetForPresentation table, you can join the Presentation and Position tables. The relation to the Template can be made through the Position table. Thus, joining the Template table to the Asset table to fetch all matching records.

You can view the demo in the below link.

Click here to view the demo in SQL Fiddle.

Hope that helps.

Script:

CREATE TABLE Presentation
(
        id          INT         NOT NULL AUTO_INCREMENT
    ,   name        VARCHAR(30) NOT NULL
    ,   PRIMARY KEY (id)
);

CREATE TABLE Template
(
        id          INT         NOT NULL AUTO_INCREMENT
    ,   name        VARCHAR(30) NOT NULL
    ,   PRIMARY KEY (id)
);

CREATE TABLE Position
(
        id          INT         NOT NULL AUTO_INCREMENT
    ,   zorder      INT         NOT NULL 
    ,   fk_template INT         NOT NULL 
    ,   PRIMARY KEY (id)
);

CREATE TABLE Asset
(
        id          INT         NOT NULL AUTO_INCREMENT
    ,   name        VARCHAR(30) NOT NULL
    ,   description VARCHAR(30) NOT NULL
    ,   PRIMARY KEY (id)
);

CREATE TABLE AssetForPresentation
(
        fk_asset        INT         NOT NULL
    ,   fk_presentation INT         NOT NULL
    ,   fk_position     INT         NOT NULL
);

INSERT INTO Presentation (name) VALUES
    ('presenation 1'),
    ('presenation 2');

INSERT INTO Template (name) VALUES
    ('template 1'),
    ('template 2');

INSERT INTO Position (zorder, fk_template) VALUES
    (1, 1),
    (2, 2);

INSERT INTO Asset (name, description) VALUES
    ('asset 1', 'asset description 1'),
    ('asset 2', 'asset description 2');

INSERT INTO AssetForPresentation (fk_asset, fk_presentation, fk_position) 
VALUES
    (1, 1, 1),
    (1, 2, 1),
    (2, 2, 1),
    (2, 2, 2);

SELECT              *
FROM                Asset A
RIGHT OUTER JOIN    AssetForPresentation AP
ON                  A.id = AP.fk_asset
RIGHT OUTER JOIN    Presentation P
ON                  P.id = AP.fk_presentation
RIGHT OUTER JOIN    Position PO
ON                  PO.id = AP.fk_position
RIGHT OUTER JOIN    Template T
ON                  T.id = PO.fk_template
WHERE               T.id = 1;

Output:

ID NAME    DESCRIPTION         FK_ASSET FK_PRESENTATION FK_POSITION ZORDER FK_TEMPLATE
-- ------- ------------------- -------- --------------- ----------- ------ -----------
1  asset 1 asset description 1    1            1            1          1        1
1  asset 1 asset description 1    1            2            1          1        1
2  asset 2 asset description 2    2            2            1          1        1

Answer 2

I think the part you're having hard time with is two conditions on JOIN to the AssetForPresentation table.

SELECT
  a.id,
  a.name,
  a.description
FROM Presentation AS p
JOIN Template AS t
  ON p.fk_template = t.id
LEFT JOIN Position AS pos
  ON pos.fk_template = t.id
LEFT JOIN AssetForPresentation AS afp
  ON afp.fk_presentation = p.id
  AND afp.fk_position = pos.id
LEFT JOIN Asset AS a
  ON a.id = afp.fk_asset
WHERE p.id = 123
ORDER BY pos.zorder ASC