[SOLVED] Counting references in a one to many relationship

[GeoffreyB]

Hi,

I am trying to display a list of records with one of the columns in the list being a count of records in a child table.

This is what I have tried to no effect:

SELECT a.*, b.(SELECT COUNT(id) FROM training_topics WHERE subjectid=a.id AND published) FROM training_subjects as a

I get this error

Error Code : 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT COUNT(id) FROM jos_training_topics WHERE subjectid=a.id AND published) F' at line 1

I guess you can't have a subquery before the from.

I am not sure have to do this with joins?

I would greatly appreciate suggestions.

Thanks

 

[xtopolis]

Can't you use a join?  It's kinda hard to tell without seeing your table structures...

SELECT COUNT(tt.id) FROM training_topics tt
JOIN training_subjects ts ON(tt.subjectid=ts.id)
WHERE tt.published = true;

Not sure what published's value should be either.

[GeoffreyB]

What I need is a result set that has all training subjects and each row has a column which is a count of published topics with a relationship to that subject.

Training Subject Structure

id int - autoincrement

name - varchar

published - tinyint (boolean either 0 or 1)

Training Topic Structure

id - int autoincrement

subjectid - int (reference to Training Subject table

title - varchar

topic - text

published - tinyint (boolean either 0 or 1

I hope this makes it clearer.

Thanks for answering

[xtopolis]

Ah, well to correct your query:

SELECT ts.*, COUNT(tt.id)
FROM training_subjects ts, training_topics tt
WHERE tt.subjectid=ts.id
AND ts.published=1
GROUP BY ts.name

changed the alias a and b to reflect the names better; ts and tt

COUNT requires a GROUP BY clause, and we're grouping by the subject name

we're only showing topics that are published

[GeoffreyB]

Thanks, Exactly what I wanted.

[GeoffreyB]

The only problem is that I don't get subjects with no published topics in the result set

[DarkWater]

You'll need a LEFT JOIN then.

 

SELECT ts.*, COUNT(tt.id) AS total FROM training_subjects AS ts LEFT JOIN training_topics AS tt ON (tt.subjectid = ts.id AND ts.published = 1) GROUP BY ts.name

 

Should work.

[xtopolis]

DarkWater's works, as well as just removing:

AND ts.published=1

 

from it

[DarkWater]

DarkWater's works, as well as just removing:

AND ts.published=1

 

from it

 

I'd think that the published column allows for you to create content without immediately making it viewable to everyone so that you can proofread it or something.  I could be wrong.