CODEWITHSUNDEEP
mysqlsql
stud91
stud91

Reputation: 1854

Intersect equivalent in MySQL

I have two Queries in MySql

  1. SELECT DISTINCT Game.mID FROM Game, SlotGame WHERE Game.gID=SlotGame.gID

  2. SELECT DISTINCT Game.mID FROM Game, TableGame WHERE Game.gID=TableGame.gID

I need to intersect the result of these two queries.

Tables as follows:

Game (gID is primary key): Game Table with gID as Primary Key SlotGame: SlotGame Table with gID as Primary Key

TableGame: enter image description here

My expected answer should be mID: 01 Note: SlotGame and TableGame will have unique values of gID

Upvotes: 2

Views: 389

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270609

You may join twice through the Game table with two different aliases, where the joining condition is equality of mID. The premise is that each of the game type tables (Slot,Table) still join against Game and return an mID. Combined, they would be a superset of mID, but if you perform an INNER JOIN on those, any not in common will be discarded from the set. It doesn't matter whether you SELECT gt.mID or SELECT gs.mID then because the remaining values are the same on both sides of the join.

SELECT DISTINCT
  gt.mID
FROM
  Game gs
  /* join Game to itself */
  INNER JOIN Game gt ON gs.mID = gt.mID
  /* and join each of the other tables to one of the Game joins */
  INNER JOIN SlotGame ON gs.gID = SlotGame.gID
  INNER JOIN TableGame ON gt.gID = TableGame.gID

Here's a sample with your rows: http://sqlfiddle.com/#!2/f57c3d/8

And proof it is extensible with a few more rows: http://sqlfiddle.com/#!2/afe66e

Upvotes: 2

FrankPl
FrankPl

Reputation: 13315

SELECT G1.mID
  FROM
    (
    SELECT DISTINCT Game.mID
    FROM Game, SlotGame
    WHERE Game.gID=SlotGame.gID
    ) G1
    JOIN
    (
    SELECT DISTINCT Game.mID
    FROM Game, TableGame
    WHERE Game.gID=TableGame.gID
    ) G2
    ON G1.mID = G2.mID

Upvotes: 1

Related Questions