CODEWITHSUNDEEP
mysqlsql
KriiV
KriiV

Reputation: 2020

Only showing results that are the same from 2 queries

So I have 1 (one) table contains a list of games. For example:

1st row: team1 = abc; team2 = xyz;

2nd row: team1 = xyz; team2 = abc;

3rd row: team1 = asd; team2 = jkl;

I've got about 4,000 rows like this. 2 teams may have played each other many times. Each row is a separate game.

A user enters a team1 name and a team2 name and I basically want to display all rows that match that. So basically all games that have those 2 teams playing.

I have tried: 

SELECT * FROM matches WHERE team1 = '$team1' OR team1 = '$team2'
UNION
SELECT * FROM matches WHERE team2 ='$team2' OR team2 = '$team1'

I've tried a few more things using an INNER JOIN but it didn't seem to work either.

Pretty new to SQL so any help would be appreciated! Thank you so much.

Upvotes: 1

Views: 39

Answers (5)

dev
dev

Reputation: 961

use UNION ALL that in place of UNION. That's it.

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

Your query would return any game in which either $team1 or $team2 has played. From your question I understand that you really want any game where $team1 played against $team2, so I'd try

SELECT * FROM matches 
WHERE 
   (team1 = '$team1' AND team2 = '$team2') OR 
   (team1 = '$team2' AND team2 = '$team1')

While you could also achive the same result using a union, it will be easier to understand and probably also quicker to do it this way, given you have an index on both columns.

Upvotes: 2

Taryn East
Taryn East

Reputation: 27747

If it can be either team in either place, you can just OR them all:

SELECT * 
FROM   matches 
WHERE  team1 = '$team1' OR team2 = '$team1' OR
       team1 = '$team2' OR team2 = '$team2'

But as others have mentioned, you probably only want the rows that have both team1 and team2 playing each other (in either combination). In which case - @Mureinik got the answer in before me.

Upvotes: 0

Sasse
Sasse

Reputation: 1128

Try the following.

SELECT * FROM matches WHERE team1 = '$team1' AND team2 = '$team2'
UNION
SELECT * FROM matches WHERE team1 ='$team2' AND team2 = '$team1'

Or you could do specify both arguments in the WHERE-clause to eliminate the use of UNION.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311123

You could do this with logical operators:

SELECT * 
FROM   matches 
WHERE  (team1 = '$team1' AND team2 = '$team2') OR
       (team1 = '$team2' AND team1 = '$team2')

Upvotes: 0

Related Questions