CODEWITHSUNDEEP
mysqlsqljoin
Jin Kwon
Jin Kwon

Reputation: 22027

(My)SQL multiple in-depth left join

Can anybody please show me how to make a view as multiple left join?

A GAME has zero or multiple LINKs (OneToMany on GAME.ID = LINK.GAME_ID)
Each LINK has exactly one of multiple STOREs (OneToOne on LINK.STORE_ID = STORE.ID)
Each STORE has exactly one of multiple PLATFORMs (OneToOne on STORE.PLATFORM_ID = PLATFORM.ID)

And I want to make a VIEW as

PLATFORM_ID (FK)    GAME_ID (FK)
--------------------------------

I mean I want to list GAMEs, by the given PLATFORM, which each has at least one or more paths through LINK and STORE to the PLATFORM

Thank you.

Upvotes: 0

Views: 406

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

SELECT DISTINCT s.PLATFORM_ID, l.GAME_ID
FROM Games g 
LEFT JOIN Links l ON g.ID = l.GAME_ID
INNER JOIN Stores ON l.STORE_ID = s.STORE_ID
INNER JOIN PLATFORMS p ON s.PLATFORM_ID = p.ID

Upvotes: 2

Anna Billstrom
Anna Billstrom

Reputation: 2492

select 
   l.game_id, p.platform_id
from
   links l,
   stores s,
   platforms p
where
   l.link_id = s.link_id
and
   p.platform_id = s.platform_id
group by 
   l.game_id, p.platform_id

Upvotes: 3

Umesh Aawte
Umesh Aawte

Reputation: 4690

Your SQL will be,

select GAME_ID 
from GAME,STORE,PLATFORM
where 
PLATFORM.ID = STORE.PLATFORM_ID
and STORE.ID = GAME.STORE_ID
and PLATFORM.ID = {some_ID}

Upvotes: 1

Related Questions