0

Hello,

What I am trying to do is use the following query:

SELECT * FROM players WHERE NOT EXISTS (SELECT * FROM raidgroups WHERE players.name=raidgroups.player) AND itemlevel>=346 AND level=85 AND NOT EXISTS (SELECT * FROM verification WHERE players.name=verification.username AND verification.organizer=1) AND suspended=0 ORDER BY RAND();

What the above does is selects a list of players from a database that meet certain requirements so that a "Raid Group" can be formed at a later time. The statement excludes people from the "verification" table because they lead the raids.

The problem is though is that many players have the same account, so when forming the raids the script can choose 2 players from a single account (which is not possible to login to 2 characters at one time), therefore, I added a new column called "account" to the players table. This new column should be distinct, I just have no idea where to throw my DISTINCT call into the statement.

Any help is appreciated.

-- Turt2Live

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by mwasif
0

You can not use DISTINCT for only one column while having multiple columns in SELECT. The alternative is GROUP BY i.e.

SELECT * FROM players WHERE NOT EXISTS (SELECT * FROM raidgroups WHERE players.name=raidgroups.player) AND itemlevel>=346 AND level=85 AND NOT EXISTS (SELECT * FROM verification WHERE players.name=verification.username AND verification.organizer=1) AND suspended=0 
GROUP BY account 
ORDER BY RAND();
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.