CODEWITHSUNDEEP
sqlsql-servermany-to-manyselect-query
MST QNB
MST QNB

Reputation: 293

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

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

enter image description here

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

enter image description here

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.

Upvotes: 1

Views: 2686

Answers (2)

Eric
Eric

Reputation: 3257

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

Upvotes: 0

Isaac
Isaac

Reputation: 3363

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

Upvotes: 1

Related Questions