[Resolved] mysql query involv. counting and multiple table

[ph3n0m]

Hi all

I am trying to do the following:

2 tables
Only common element is an id field

Table 1: id field is called fileid
Table 2: id field is called id
it also has a field called filename

What I am trying to do is

Count the number of times fileid appears in table 1. Then using fileid, cross reference with table 2 and find out what the filename is

so far I have had little success

Below is the query I am using, but as you will see it doesnt work.

Can anyone show me where I have gone wrong, or what I should be using (praying it doesnt involved JOINs)

select t1.fileid as fileid, DISTINCT COUNT(t1.fileid) AS fileid_count, t2.filename as filename from owners_files as t1, files as t2 order by lower(filename) asc

[AstroTeg]

I'd try something like this:

select t1.fileid as fileid, COUNT(t1.fileid) AS fileid_count, t2.filename as filename from owners_files as t1, files as t2 GROUP BY t1.fileid, ts.filename order by lower(filename) asc

I removed the DISTINCT. Its more designed for grouping without using GROUP BY. But for COUNT() to work, you have to use GROUP BY.

[ph3n0m]

Thanks for that.

I got what I need by using the following

select jay.fileid as fileid, COUNT (jay.fileid) AS fileid_count, bob.filename as filename from owners_files as jay, files as bob WHERE jay.fileid=bob.id GROUP BY bob.filename, jay.fileid having COUNT(jay.fileid)>=2 order by lower(filename) asc