How to select multiple many to many in relation with a single table

Question

I'm currently working with database, but I've got stuck with a select query. However, I'm not database expert. The query should return the data from a table that has two relationships of many to many.

This is my tables Diagram that would shows the concept of my question

The Select Query should View three columns, which are VidTbl.Name, ActorTbl.Name and SubTitelTbl.name.

So, I've read and search in the Internet and I've given tries

First try

SELECT  
        VidTbl.NAME AS Video_Titel_Name,
        ActorTbl.NAME AS Actor_Name

FROM    ActorInVid 

INNER JOIN VidTbl

ON VidTbl.Id = ActorInVid.FKVidId 

INNER JOIN ActorTbl

ON ActorTbl.Id = ActorInVid.FKActorId



UNION all

SELECT 
        VidTbl.NAME AS Video_Titel_Name,
        SubTitelTbl.NAME AS SubTitel_Langu

FROM SubTitelInVid

INNER JOIN VidTbl

ON VidTbl.Id = SubTitelInVid.FKVidId

INNER JOIN SubTitelTbl 

ON SubTitelTbl.Id = SubTitelInVid.FKSTId

The Result I've got, it was wrong

Then I tried another way to solve this problem, but again I've got another error

second try

    SELECT Temp1.* 
From  (SELECT    VidTbl.Id        AS Video_Id,
                 VidTbl.NAME      AS Video_Titel_Name,
                 ActorTbl.NAME    AS Actor_Name

       FROM      ActorInVid  
       INNER JOIN VidTbl
               ON VidTbl.Id = ActorInVid.FKVidId 
       INNER JOIN ActorTbl
               ON ActorTbl.Id  = ActorInVid.FKActorId) AS  Temp1

SELECT Temp2.*
FROM  (SELECT VidTbl.Id           AS Video_Id,
              SubTitelTbl.NAME    AS SubTitel_Langu 
      FROM SubTitelInVid
INNER JOIN VidTbl
        ON VidTbl.Id      = SubTitelInVid.FKVidId
INNER JOIN SubTitelTbl 
        ON SubTitelTbl.Id = SubTitelInVid.FKSTId) AS Temp2


SELECT * 
FROM   VidTbl
INNER JOIN Temp1 
        on Temp1.Video_Id = VidTbl.Id
INNER JOIN Temp2
        on Temp2.Video_Id = VidTbl.Id

The error, I've got in the last select that was wrong

Thanks a lot for your help any ways I wish that my question is clear and useful Thanks again.

Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ One suggestion I would make it to make your column names consistent across the system. And don't be scared to use actual words. VidID should have the same name in each and every table. It is always the VidID...unless it an abbreviation for Video in which case it should be VideoID. I would also avoid adding the FK and TBL prefix and suffixes to everything. This is known as tbl-ing and is frowned on. Why not table names like Video, Actor, ActorVideo, SubTitle etc... — Sean Lange, May 03 '16 at 15:26

score 1 · Accepted Answer · answered May 03 '16 at 15:24

You are close. This should work...

SELECT 
VidTbl.Name, 
ActorTbl.Name, 
SubTitelTbl.name
FROM VidTbl
INNER JOIN ActorInVid ON VidTbl.Id = ActorInVid.FKVidId 
INNER JOIN ActorTbl ON ActorTbl.Id = ActorInVid.FKActorId
INNER JOIN SubTitelInVid ON VidTbl.Id = SubTitelInVid.FKVidId
INNER JOIN SubTitelTbl ON SubTitelTbl.Id = SubTitelInVid.FKSTId

score 0 · Answer 2 · answered May 03 '16 at 15:26

0

SELECT DISTINCT vt.Name, at.Name, st.Name
FROM VidTbl vt
JOIN ActionInVid aiv ON aiv.VidId = vt.Id
JOIN SubtitleInVid siv ON siv.VidId = vt.Id
JOIN ActorTbl at ON at.Id = aiv.ActorId
JOIN SubTitleTbl st ON st.Id = siv.STId

answered May 03 '16 at 15:26

Eric

3,165
1
19
25

How to select multiple many to many in relation with a single table

2 Answers2