how i cant select from mysql without duplication

Question

my problem is too complex

now i have files table

I selected from it the files which added in last seven days. it returns many ids like 1,2,3,4,5 ok it's great. Now the problem:

i have tutorial table

every tutorial has some files of files table

now i selected from files which last added from 7 days

and make list ordered by files downloaded count desc and after that I select from tutorial which equal tutorial id in the files table this mean

files_id tutorial_id
   1          2

files_id tutorial_id
   2          2

this mean two files in one tutorial

now the tutorial

tut_id tut_title
 1        tite test

downloaded in last seven days by files list

file_id = 1 this mean tutorial id = 2
file_id = 2 this mean tutorial id = 2

This means I have one tutorial and two files in the most downloaded list will duplicate the tutorial title twice.

its really complex.

Answer 1

SELECT DISTINCT ...?

Answer 2

If I understand you correctly, you basically want to list the tutorials most downloaded versus the files most downloaded.

In which case, you can use the following query to get the TOP 10:

SELECT tutorials.tut_id,
       tutorials.tut_title,
       MAX(files.file_downloads) AS tut_downloads
  FROM tutorials, tutorial_files, files
  WHERE tutorials.tut_id = tutorial_files.tut_id
    AND files.file_id    = tutorial_files.file_id
  GROUP BY tutorials.tut_id, tutorials.tut_title
  ORDER BY tut_downloads DESC
  LIMIT 10;

You can change the MAX by a SUM depending on how you want to calculate the numbers of downloads per-tutorial.

Same, but limits it to the files added in the last 7 days:

SELECT tutorials.tut_id,
       tutorials.tut_title,
       MAX(files.file_downloads) AS tut_downloads
  FROM tutorials, tutorial_files, files
  WHERE tutorials.tut_id = tutorial_files.tut_id
    AND files.file_id    = tutorial_files.file_id
    AND files.file_added >= DATE_SUB(NOW(), INTERVAL 7 DAYS)
  GROUP BY tutorials.tut_id, tutorials.tut_title
  ORDER BY tut_downloads DESC
  LIMIT 10;

If this is not what you want, I am sorry. Your question is very unclear.

Answer 3

Use Limit 1 to get the top most row after ordering. This will return only the first row, which is the most download file (Remember you ordered it, so the most download file will be on top).