CODEWITHSUNDEEP
mysqlsqljoin
lubilis
lubilis

Reputation: 4160

Query sql to find row's count from another table

In my MySQL db i have these 3 tables:

ACCOUNT (primary key is name)
 name
 directory
 birthdate
 sex

FILES (primary key is id)
 id (autoincrement)
 name (account foreign key)
 filename
 uploaded_date

CHECKED (primary key is the couple name - filename)
 name (account foreign key)
 filename (files foreign key)

I want to make a query that shows a resulting table like this:

RESULT
 name - directory - birthdate - sex - filename uploaded_date - checkedCount

Where in checkedCount column i want to have the existing row's count number in CHECKED table for every unique filename.

I tried with this but i had no success:

SELECT * FROM (SELECT * FROM account NATURAL JOIN files) AS table_alias LEFT JOIN (SELECT *, count(*) AS checkedCount FROM checked) AS checked_alias ON table_alias.filename = checked_alias.filename

Currently in ACCOUNT table there are only 2 row, in FILES table 6 rows and in CHECKED tables there are 6 rows with the same name from ACCOUNT and all others 6 unique filenames from FILES.

I can't understand why in my resulting table in checkedCount columns i have all values setted to null, except one that is setted to 6. My goal should be to have all six rows with checkedCount = 1 since every FILE in FILES table is used one times in CHECK table.

Upvotes: 1

Views: 799

Answers (3)

Robert Mennell
Robert Mennell

Reputation: 2052

So I'm guessing you know how count works right? By using a count you need a group by clause.

Example:

SELECT count(name) as count, name FROM CHECKED GROUP BY name

Now here's the problem. You have a big nasty join that you want to do a count on. So lets join the tables, alias them, and then do a count on a field.

SELECT ACCOUNT.name AS name, ACCOUNT.directory AS directory, ACCOUNT.birthDate AS birthdate, ACCOUNT.sex AS sex, FILES.filename AS filename, FILES.uploaded_date AS uploaded_date, count(CHECKED.filename) AS checkedCount
FROM ACCOUNT JOIN FILES ON ACCOUNT.name = FILES.name JOIN CHECKED ON FILES.filename = CHECKED.filename GROUP BY CHECKED.filename;

This... Big... Long... Confusing... join statement should do what you want, but I would also take a look at MySQL Count and MySQL Aliasing to make things prettier.

Upvotes: 1

mynawaz
mynawaz

Reputation: 1594

try this

SELECT
    a.*, f.filename, f.uploaded_date, IFNULL(c.cnt, 0) as 'checkedCount'
FROM
    account a
    LEFT OUTER JOIN files f ON a.name = f.name
    LEFT OUTER JOIN (SELECT name, COUNT(*) FROM checked GROUP BY 1) c ON a.name=c.name

Upvotes: 1

Felippe Rangel
Felippe Rangel

Reputation: 94

If I got this straight, the following godê should work for you:

SELECT 
 A.name
,A.directory
,A.birthdate
,A.sexfilename
,F.uploaded_date
,(CASE WHEN C.filename IS NOT NULL THEN 1 ELSE 0 END) AS checkedCount
FROM 
ACCOUNT A
LEFT JOIN FILES F
     ON  A.name = F.name
LEFT JOIN CHECKED C
     ON F.filename = C.filename

Try it out and let me know.

I do not know what your DB is about, but can't your "CHECKED" table be converted into a "checked" column inside your FILES table? As it is a 1-1 relation? Maybe that will make it easier.

Upvotes: 0

Related Questions