0

I am using a local MAMP connection and creating databases in phpMyadmin. I have a Fantasy Football Database that I want to return all combinations of a submitted lineup where the salary of the NFL players in the contest are less than or equal to a combined 50,000 AND the Fantasy Points Scored is greater than or equal to 200. So a valid lineup consists of a QB, RB1, RB2, WR1, WR2, WR3, TE, FLEX, and DST. A valid lineup can be only submitted if the combined cost of the 9 players in the lineup is less than or equal to 50,000 (for example, the QB costs 10,000, RB1 costs 5,000, RB2 costs 6,000 etc for all 9 slots which must be less than or equal to 50,000). Also, each lineup slot must have a unique player even though there are multiple Running Back and Wide Receiver slots. The FLEX slot can be either a RB, WR, or TE but again, it can't be any of the players in any of the other slots. I am trying to get the results of a contest and return a data set of all possible valid lineups that scored over 200. My database is NOT normalized. I do know how to normalize it and I can if it will make the results better/faster. But for now, I'm just trying to test the query. Here is a link to a sample data set https://docs.google.com/spreadsheets/d/1ECXtOj3MALgQNVMrr1wOlNso2iipqg2jzP6Ty9DZ0JA/edit?usp=sharing I have tried various SQL SELECT statements but they all freeze up and I can't get a result. I have tried limiting the number of results returned but to no avail. Here is basically the SELECT statement that I want to make:

SELECT DISTINCT QB, QB.Fpts, RB1, RB1.Fpts, RB2, RB2.Fpts, WR1, WR1.Fpts, WR2, WR2.Fpts, WR3, WR3.Fpts, TE, TE.Fpts, FLEX, FLEX.Fpts, DST, DST.Fpts
FROM QB, RB1, RB2, WR1, WR2, WR3, TE, FLEX, DST
WHERE
(QB.Salary + RB1.Salary + RB2.Salary + WR1.Salary + WR2.Salary + WR3.Salary + TE.Salary + FLEX.Salary + + DST.Salary <= 50000)
AND
(QB.Fpts + RB1.Fpts + RB2.Fpts + WR1.Fpts + WR2.Fpts + WR3.Fpts + TE.Fpts + FLEX.Fpts + DST.Fpts >= 200)
LIMIT 1000;

How can I make it where I can return values from this query and is there a way to save the results to a text file?

Jason McCoy
  • 63
  • 7
  • 1
    You can't put just a table name in the `SELECT` list, just column names and expressions. – Barmar Feb 07 '22 at 23:06
  • Do you really have 3 separate `WRx` tables, rather than one table with all the wide receivers? – Barmar Feb 07 '22 at 23:09
  • 1
    This is going to be slow because generating the full cross product between all these tables creates an enormous intermediate table, from which it then has to filter by the total salary and points. – Barmar Feb 07 '22 at 23:10
  • Yes, I do have 3 WR tables right now. Like I said, this database is not normalized. I don't know if normalizing it would make the query faster or not. Yeah, its a lot of data. – Jason McCoy Feb 07 '22 at 23:16
  • Barmar, the columns are the same name as the tables, so I don't have the table names in the first part of the select. Those are the column names. – Jason McCoy Feb 07 '22 at 23:17
  • use into out fule see https://stackoverflow.com/questions/1119312/mysql-export-into-outfile-csv-escaping-chars – nbk Feb 07 '22 at 23:18
  • _" I don't know if normalizing it would make the query faster or not"_ , just try it. From what I can see, you can make 1 table for the players with different positions then avoid the need to join multiple _position_ table. Anyway, your sample data in the google drive require access. Can you make it public or else can you create the sample data in [a MySQL fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0) instead? – FanoFN Feb 08 '22 at 00:36
  • Sorry, I thought I had already made it public. Here's the link – Jason McCoy Feb 08 '22 at 00:48
  • 1
    From your sample data, the cross joins of just the three `WR` tables already returned around 4 million rows.. cross join that to the two `RB` tables will returned about 40+ billion rows.. – FanoFN Feb 08 '22 at 04:05

1 Answers1

0

You need additional conditions requiring that the players not be equal:

SELECT DISTINCT QB, QB.Fpts, RB1, RB1.Fpts, RB2, RB2.Fpts, WR1, WR1.Fpts, WR2, WR2.Fpts, WR3, WR3.Fpts, TE, TE.Fpts, FLEX, FLEX.Fpts, DST, DST.Fpts
FROM QB, RB1, RB2, WR1, WR2, WR3, TE, FLEX, DST
WHERE
WR1 != WR2 AND WR1 != WR3 AND WR2 != WR 3 
AND
RB1 != RB2 
AND
FLEX != WR1 AND FLEX != WR2 AND FLEX != WR3 AND FLEX != RB1 AND FLEX != RB2 AND FLEX != TE 
AND
(QB.Salary + RB1.Salary + RB2.Salary + WR1.Salary + WR2.Salary + WR3.Salary + TE.Salary + FLEX.Salary + + DST.Salary <= 50000)
AND
(QB.Fpts + RB1.Fpts + RB2.Fpts + WR1.Fpts + WR2.Fpts + WR3.Fpts + TE.Fpts + FLEX.Fpts + DST.Fpts >= 200)
LIMIT 1000;
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • 2
    to keep this from being absolutely brutal, you want to do explicit joins and only join at each step records that don't already exceed the salary limit. it's still not an appropriate problem to attempt to solve in sql, but googllng "knapsack sql" may provide some hints to help. – ysth Feb 08 '22 at 00:49