How to make MySQL DISTINCT work with multiple columns using table join?

Question

How can I made the query below select only one record? Each game has two records (one record for each team). If there are nine (9) games there would be eighteen (18) records. I want to select only one record, not both records, for each game.

If I use DISTINCT with only one column (game_id_2) it works fine or returns only nine records. However, if I try to add more columns the DISTINCT directive no longer works.

SELECT DISTINCT
B.game_id_2,
B.GmeYear,
B.GmeMonth,
B.GmeDay,
B.GmeDate,
B.GmeTime,
B.GmeOrd,
B.Home,
B.DivPlay,
L.Instit,
FROM BsbGme B LEFT JOIN LeagueTeam L
ON B.team_id = L.team_id
WHERE B.NonD1=''
AND B.team_id IN ($participant_str)
AND B.GmeMonth = $GameMonth
AND B.GmeDay = $GameDay
ORDER BY B.game_id_2 ASC

According to w3schools dot come the DISTINCT directive is supposed to work with multiple columns. But it's not working with multiple columns in my example.

w3schools example:

SQL SELECT DISTINCT Syntax SELECT DISTINCT column_name,column_name FROM table_name;

http://www.w3schools.com/sql/sql_distinct.asp

I have looked at several of the other answers to this same question on StackOverflow but I can't get any of them to work.

Thank you in advance.

Răducanu Ionuţ · Accepted Answer · 2015-08-31 15:55:18Z

1

try using brackets DISTINCT (your, columns) or use GROUP BY.

answered Aug 31, 2015 at 15:55

Răducanu Ionuţ

4302 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Răducanu Ionuţ Over a year ago

And another thing, can you try it without the ',' before FROM

Harvey Over a year ago

Thank you for the help.It worked! I couldn't get the brackets to work. I used the GROUP By. The comma was just a typo. THANKS AGAIN!

Collectives™ on Stack Overflow

How to make MySQL DISTINCT work with multiple columns using table join?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related