0

I have two tables. One of them named files and there is al list of all files. the second table called payments, and there is in there a list of payments for some files.

Payments:

id | fileid | {...}
 1   2
 2   3
 3   2

Files:

id | {...}
1
2
3

I want to select all files, and join the table payments to order by count of this table.

In this case, the first row will be file #2, because it repeats the most in the payments table.

I tried to do it, but when I do it - not all of the rows are shown!

I think it happens because not all of the files are in the payments table. So in this case, I think that it won't display the first row.

Thanks, and sorry for my English

P.S: I use mysql engine

** UPDATE ** My Code:

            SELECT      `id`,`name`,`size`,`downloads`,`upload_date`,`server_ip`,COUNT(`uploadid`) AS numProfits
            FROM        `uploads` 
            JOIN        `profits`
            ON          `uploads`.`id` = `profits`.`uploadid`
            WHERE       `uploads`.`userid` = 1
            AND         `removed` = 0
            ORDER BY    numProfits
HTMHell
  • 5,761
  • 5
  • 37
  • 79

3 Answers3

2

As others have noted you need to use LEFT JOIN. - This tells MySQL that entries from the tables to the left should be included even if no corresponding entries exists in the table on the right.

Also you should use GROUP BY to indicate how the COUNT should be deliminated.

So the SQL should be something like;

SELECT Files.ID, count(Payments.FileID) as numpays FROM
Files
LEFT OUTER JOIN
Payments
ON Files.id=Payments.FileID
GROUP BY files.ID
ORDER BY numpays desc

SQL Fiddle

Taemyr
  • 3,407
  • 16
  • 26
0

Try LEFT JOIN - in MySQL, the default JOIN is actually an INNER JOIN. In an INNER JOIN, you will only get results back that are in both sides of the join.

See: Difference in MySQL JOIN vs LEFT JOIN

And, as noted in the comments, you may need a GROUP BY with your COUNT as well, to prevent it from just counting all the rows that come back.

Community
  • 1
  • 1
SubSevn
  • 1,008
  • 2
  • 10
  • 27
  • It returns only one row. – HTMHell Aug 29 '13 at 13:56
  • Is the `uploads` table the same as the `files` table you mention in your example? I'm having trouble figuring out how you would get fewer rows doing a `LEFT JOIN` (which by definition returns the same or greater number than an `INNER JOIN`). – SubSevn Aug 29 '13 at 13:59
  • I wrote `files` but I ment `uploads` – HTMHell Aug 29 '13 at 14:00
  • `INNER JOIN` returned only one row too, because there is one row in `payments` table. but with `LEFT JOIN` it returns one row who isn't in the `payments` table – HTMHell Aug 29 '13 at 14:02
  • 2
    I think the reason he gets only one row has to do with the interaction between count and lack of GROUP BY. Although I am not sure about the details. Specifiying the group resolves the issue. - It's true that INNER JOIN also only returns one row, but there are three rows in the payments table- hence the count gives three. – Taemyr Aug 29 '13 at 14:04
  • 1
    @ArielAharonson Count is an aggregate operator - I would check, when you perform your query that returns one row, whether or not the `COUNT` that comes back is three, if that's the case, add the appropriate `GROUP BY`. – SubSevn Aug 29 '13 at 14:09
0

Try this:

select B.fileid,A.{}.....
from
(select id,.....
from files A
inner join
(select count(*),fileid,.....
from payments
group by fileid) B
on files.id=payments.fileid)

I hope this helps. I'm assuming that all ID in files table are unique. In this answer, you can apply an order by clause as per your wish. I've left the select statement to you to select whatever data you want to fetch.

As far as your problem is described, I think this should work. If any problems, do post a comment.

Rijul
  • 515
  • 7
  • 19