'SQL Join two tables with a third table

I want to create a query that selects the columns PlayerName , PlayerNumber, Location, PartnerName, PartnerNumber, Location from the following tables:

  • Players (Columns: Id, PlayerName, PlayerNumber, LocationId)
  • Partners (Columns: Id, PartnerName, PartnerNumber, LocationId)
  • Locations (Columns: Id, Location)

But I cannot figure out how to do that. Does anyone have any ideas?

mysqlsqljoin


Solution 1:[1]

Simple straight way

select PL.PlayerName , PL.PlayerNumber, L.Location, PT.PartnerName, PT.PartnerNumber
from  Players PL, Location L, Patners PT
where L.id = PL.LocationId and L.id = P.LocationId

This can also be done using UNION, but am not very sure about that

Solution 2:[2]

Try this.. I am not sure 

select pl.Id,pl.PlayerName,pl.PlayerNumber,pa.ID,pa.PartnerName,pa.PartnerNumber,l.Location 
from Players as  pl,Partners as pa ,Location as l 
where pl.LocationId = pa.LocationId and l.LocationId = pl.LocationId
and pa.LocationId = l.LocationId
AND l.LocationId = <some id>

Solution 3:[3]

Select PlayerName , PlayerNumber, Location, PartnerName, PartnerNumber
from Players pl
inner join Partners pa on pl.LocationId=pa.LocationId
inner join Location L on L.id=pa.LocationId
where Location = 'String to search for'

Solution 4:[4]

select
  PlayerName,
  PlayerNumber,
  Location,
  PartnerName,
  PartnerNumber
from players
  left join locations
    on locations.id = players.LocationId
  left join partners
    on partners.LocationId = locations.id

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Fahim Parkar
Solution 3 Chris
Solution 4 Muhammad Raheel

Related Questions