Need data from 2 columns in 1 query

[MattNolan]

Edit: Should say 2 tables in 1 query

I need to select:

select * from blogs;

and I need to pair this with

select count(*) from comments group by aid;

where aid is in both blogs as the main key, and aid is just a regular field in the comments table

[Fumigator]

The way to do this is to take the simple COUNT(*) query, grouping by the id:


SELECT COUNT(*), table_id
FROM table2
GROUP BY by table_id


And make that a TEMP table, joined with the main table.


SELECT * FROM main_table as m
JOIN (
SELECT COUNT(*), main_table_id
FROM table2
GROUP BY main_table_id) as count_table
ON m.table_id = count_table.main_id

[Old Pedant]

Why the inner SELECT???

Why not a simple JOIN??


SELECT B.field1, B.field2, B.field3, COUNT(c.aid) AS howManyComments
FROM blogs AS B INNER JOIN comments AS C
ON B.aid = C.aid
GROUP BY B.field1, B.field2, B.field3

Now, if any blogs record does *NOT* have any comments as of yet, then that blogs records will not appear at all.

If you want all blogs records to appear, even if the count of comments is zero, then just change the INNER JOIN to a LEFT JOIN.

[Fumigator]

To avoid grouping by every selected column.

[Old Pedant]

To avoid grouping by every selected column.

Hmmm...I'd actually think that *forcing* him to select the individual columns and then group by them might be a good idea.

But you make a point worth considering, for sure.

My gut feel is that you won't see a performance difference between the two, but maybe I'm wrong. Will have to test that out one of these days.

[MattNolan]

Great solutions guys, definitely helped me, I was completely unsure of the join syntax and this is perfect.