Query sql to find row's count from another table

Question

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.

mynawaz · Accepted Answer · 2015-09-30 18:47:44Z

1

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

answered Sep 30, 2015 at 18:47

mynawaz

1,5941 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lubilis Over a year ago

Thanks, after some attempts it worked. Only one warning, i think it's better to use GROUP BY filename since if column table order is modified, the query's result changes

mynawaz Over a year ago

@lubilis, Sure, to do that just change the last line to LEFT OUTER JOIN (SELECT filename, COUNT(*) FROM checked GROUP BY 1) c ON f.filename=c.filename

Robert Mennell · Accepted Answer · 2015-09-30 22:36:21Z

1

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.

edited Sep 30, 2015 at 22:36

answered Sep 30, 2015 at 18:44

Robert Mennell

2,05214 silver badges25 bronze badges

3 Comments

lubilis Over a year ago

Thanks, this should be the right way. I've only one issue now. With the GROUP BY CHECKED.name in my results all those files that don't exist in CHECKED table are skipped. I tried with LEFT OUTER JOIN with no success

Robert Mennell Over a year ago

Whoops. Hmm lets try an edit with count(CHECKED.filename) and GROUP BY CHECKED.filename instead to see if that does what you want @lubilis

lubilis Over a year ago

Sorry, i was already trying with CHECKED.filename with no success. I think that using this query it's normal that some values are skipped

Felippe Rangel · Accepted Answer · 2015-09-30 18:41:52Z

0

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.

answered Sep 30, 2015 at 18:41

Felippe Rangel

1942 silver badges7 bronze badges

2 Comments

lubilis Over a year ago

I can't convert CHECKED table into a "checked" column inside FILES table since one file can be checked from one user but not from another one

lubilis Over a year ago

Files are unique, there can be many distinct files and every file can be checked or not from a distinct user

Collectives™ on Stack Overflow

Query sql to find row's count from another table

3 Answers 3

2 Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related