Do I use Except?
Hi, I'm building an online fantasy sports game. I want to present a list of players available to purchase. HOwever I need to filter out the players the user already has. I have a table which includes all of the players' information - name, position, price, etc I have a table which includes all of my transactions - managerid, playerid, dateofpurchase, dateofsale I need to select all players in the players table except those that occur in the transactions table which match the 'managerid'. I'm stuck! Any help would be greatfully received! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do I use Except?
Look up Left JOIN. This join will solve your problem. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Matt MacLeod [mailto:[EMAIL PROTECTED] --Sent: Friday, September 19, 2003 8:38 AM --To: [EMAIL PROTECTED] --Subject: Do I use Except? -- --Hi, -- --I'm building an online fantasy sports game. I want to present a list of --players available to purchase. HOwever I need to filter out the players --the user already has. -- --I have a table which includes all of the players' information - name, --position, price, etc --I have a table which includes all of my transactions - managerid, --playerid, dateofpurchase, dateofsale -- --I need to select all players in the players table except those that --occur in the transactions table which match the 'managerid'. -- --I'm stuck! Any help would be greatfully received! -- --Matt -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do I use Except?
Matt, On most platforms, you would generally do a sub select of the form select playerid from players p where not exists ( select * from myplayers m where m.player_id = p.player_id ) However, as sub selects are only supported in mysql 4.1, you'll need to see section 1.7.4.1 Sub queries in the manual on how to change this into a join supported in mysql prior to 4.1 Andy -Original Message- From: Matt MacLeod [mailto:[EMAIL PROTECTED] Sent: 19 September 2003 16:38 To: [EMAIL PROTECTED] Subject: Do I use Except? Hi, I'm building an online fantasy sports game. I want to present a list of players available to purchase. HOwever I need to filter out the players the user already has. I have a table which includes all of the players' information - name, position, price, etc I have a table which includes all of my transactions - managerid, playerid, dateofpurchase, dateofsale I need to select all players in the players table except those that occur in the transactions table which match the 'managerid'. I'm stuck! Any help would be greatfully received! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
