SQL How to check if 2 columns from one table matches a conditional within second table-CodePudding

Distances Table:

StartTeam	DestinationTeam	Distances
Atlanta Hawks	Boston Celtics	1234
Brooklyn Nets	Atlanta Hawks	212

Stadiums Table:

TeamName	Enabled
Atlanta Hawks	1
Boston Celtics	0
Brooklyn Nets	1

I need a sql query that will check if the StartTeam and DestinationTeam are both Enabled within the Stadiums table.

I tried this query to check if both teams are within the Stadiums table but it's clearly not correct. Would love some help!

SELECT *
FROM Distances d
LEFT JOIN Stadiums s
ON d.StartTeam = s.[Team Name]
WHERE s.Enabled AND (
    SELECT *
    FROM Distances d
    LEFT JOIN Stadiums s
    ON d.DestinationTeam = s.[Team Name]
    WHERE s.Enabled = 1
)
ORDER BY d.DestinationTeam ASC

So the working query would spit out the second row since both Brooklyn Nets and Atlanta Hawks are enabled. The first row wouldn't be returned since Boston Celtics is not enabled.

DESIRED OUTPUT:

StartTeam	DestinationTeam	Distances
Brooklyn Nets	Atlanta Hawks	212

CodePudding user response：

Join Distances to Stadiums twice:

SELECT d.*
FROM Distances d
INNER JOIN Stadiums s1 ON s1.TeamName = d.StartTeam
INNER JOIN Stadiums s2 ON s2.TeamName = d.DestinationTeam
WHERE s1.Enabled   s2.Enabled = 2;

Note that the criterion in the WHERE clause is asserting that both the start and destination teams are enabled.

CodePudding user response：

select * from Distances
where 1 = (
    select min(Enabled) from Stadiums
    where Teamname in (StartTeam, DestinationTeam)
)

To get additional columns and stick with your original line of thought:

SELECT * FROM Distances d
    INNER JOIN Stadiums s ON d.StartTeam = s.[Team Name]
WHERE s.Enabled = 1 AND EXISTS (
    SELECT * FROM Stadiums s2
    WHERE s2.[Team Name] = d.DestinationTeam
      AND s2.Enabled = 1
)
ORDER BY d.DestinationTeam ASC

Of course you'll only have access to start team columns in the output that way.