2

I have the following tables:

pages:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| page_id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| type       | varchar(20)  | NO   |     | NULL    |                |
| parent_id  | int(11)      | NO   |     | NULL    |                |
| title      | varchar(255) | NO   | MUL | NULL    |                |
| text       | longtext     | NO   | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

custom:

+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| page_id | int(10) unsigned | NO   | PRI | NULL    |       |
| key     | varchar(255)     | NO   | PRI | NULL    |       |
| value   | longtext         | NO   |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+

Whit the following query I get all the entries from table pages where type = 'questions' and get the values from table custom where key = 'votes'.

The problem here is this. In table pages I want to count all the entries with type = 'comments' AND parent_id = 'page_id'

SELECT * FROM pages AS P  
LEFT JOIN custom AS C
    ON P.page_id = C.page_id AND  
        C.key = 'votes'
WHERE  
    P.type = 'questions'  
ORDER BY P.date DESC, C.value DESC

The problem is I have to inset somewhere SELECT COUNT(*) AS posts_count FROM pages WHERE page_id = parent_id and the value from posts_count must be available in the query, because I want to order the entries by the order of comments AND after that by the votes from table custom

Ivan Dokov
  • 4,013
  • 6
  • 24
  • 36

1 Answers1

0

It seems like you could use a subquery for to get this result:

SELECT p1.page_id,
  p1.type,
  p1.parent_id,
  p1.title,
  p1.text,
  Coalesce(p2.TotalComments, 0) TotalComments,
  c.key,
  c.value
FROM pages AS P1
LEFT JOIN
(
  select count(*) TotalComments, parent_id
  from pages
  where type = 'comments'
  group by parent_id
) p2
  on p1.parent_id = p2.parent_id
LEFT JOIN custom AS C
  ON P1.page_id = C.page_id 
  AND C.key = 'votes'
WHERE P.type = 'questions'  
ORDER BY P1.date DESC, C.value DESC
Taryn
  • 242,637
  • 56
  • 362
  • 405