MST QNB MST QNB - 7 months ago 10
SQL Question

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.

Answer

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