如何根据给定条件连接多个表以获取结果？

问题描述：

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.

我一直在看这个太久了，我的大脑感觉像意大利面条。有人可以帮我解决一些问题吗？ p>

表 em>： p>

  Presentation 
--  ---------- 
id int 
name varchar 
fk_template int（引用template.id）
 
Template 
 -------- 
id int 
name varchar 
 
Position \  n -------- 
id int 
zorder int（如果它是给定模板的第1，第2，第3位置）
 nfk_template int（引用Template.id）
 
Asset 
 ----  -  
id int 
name varchar 
description varchar 
 
AssetForPresentation 
 -------------------- 
fk_asset int（引用Asset.id）
fk_presentation int（引用 Presentation.id）
 nfk_position int（引用Position.id）
  code>  pre> 
 
 我想想 em>我此时需要提出的问题基本上是 “给我这个模板的所有资产及其位置。” p> 
 
 
你看，当一个演示文稿被带来时，它有一个特定的模板。 每个模板都有指定的位置，每个位置都有一个资产或NULL。 p> 
 
 

我需要能够为演示文稿使用的特定模板引入所有资产及其各自的位置。  我如何查询类似内容？ strong>  p> 
 
 
我希望这对您有意义。 p> 
  div>

答

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

答

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

如何根据给定条件连接多个表以获取结果？

相关推荐