PHP & Mysql - Left Outer Join between two tables

I have two tables called 'events' and 'topics' each table can have many comments.

What I need to do is list all the events and topics with the amount of comments for each row. I've managed to return all the topics, which works great but I don't know how I can add the events table to the MySql. The comments and events table fields are listed below. Can anyone help me with this query?

Events:

• ID
• Event_Name

开发者_如何学CComments:

• post_id <-- the releated id for either the events or topics table

• table <-- The table that the row belongs to so either topics or events

  SELECT 
    t.id, t.title, c.created_at, 
    IF(ISNULL(c.allComments), 0, c.allComments) AS totalComments
  FROM topics AS t
  LEFT OUTER JOIN (
      SELECT created_at, post_id, COUNT(*) AS allComments 
      FROM comments
      GROUP BY post_id
  ) AS c ON c.post_id = t.id
  ORDER BY tc.created_at DESC, c.allComments DESC
  

---

Sounds like events and topics should be the same table.

Still, I think we can do this with a UNION. Events and Topics have the same columns i hope? (Or at least the same important ones?)

(SELECT c.table as event_or_topic, e.*, count(C.table), MAX(C.created_at) as latest_c
FROM events E LEFT JOIN comments C on (C.post_id = E.id)
WHERE C.table = 'Events' 
GROUP BY C.post_id)
UNION
(SELECT c.table as event_or_topic, t.id*, count(C.table), MAX(C.created_at) as latest_c
FROM topics T LEFT JOIN comments C on (C.post_id = E.id)
WHERE C.table = 'Topics' 
GROUP BY C.post_id)
ORDER BY latest_c

Notice that the ORDER BY applies to the whole UNION, not the individual SELECTs.

The use of LEFT JOIN should allow those rows without Comments to still show. I think the problem is that we have parts of our select dependent on comments (ie - C.table, ordering on last comment, etc). The count should be fine - will just be zero if there are no comments.

You might need to change the SELECT part slightly. I'd like to display C.table so you know whether a row is a topic or event, but im afraid it might screw up the count. Do you need anything from comments besides the count? You use some columns other than post_id and table in your query that you neglected to explain in your question.

You still have columns I don't know what they are, like Comment's zoneTable

---

Try this:

SELECT t.id, t.title, c.created_at, COUNT(c.allComments) AS totalComments FROM topics AS t LEFT JOIN comments c ON t.id=c.post_id GROUP BY t.id ORDER BY tc.created_at DESC, c.allComments DESC

---

If I understand your question you have 3 tables:

-Events

-Topics

-Comments

If that is true something like this should extract all the data:

SELECT *
FROM events,topics
LEFT JOIN comments ON post_ID = ID
ORDER BY date DESC

Hope i'm along the right lines!

W.

---

I've got it working. If anyone knows of a better and an efficient way of doing this, then please let me know:

(SELECT t.id, t.title, tc.dateCreated AS commentDate, 
IF(ISNULL(tc.allComments), 0, tc.allComments) AS totalComments,
t.LastActive as dateChanged
    FROM Events AS t
    LEFT OUTER JOIN (
          SELECT MAX(created_at) AS dateCreated, post_id,
          COUNT(*) AS allComments 
          FROM comments
          GROUP BY post_id
          ) AS tc ON tc.post_id = t.id)
UNION
(SELECT t.id, t.title, tc.dateCreated AS commentDate, 
IF(ISNULL(tc.allComments), 0, tc.allComments) AS totalComments,
t.LastActive as dateChanged 
    FROM topics AS t
    LEFT OUTER JOIN (
          SELECT MAX(created_at) AS dateCreated, post_id,
          COUNT(*) AS allComments 
          FROM comments
          GROUP BY post_id
          ) AS tc ON tc.post_id = t.id)
ORDER BY commentDate DESC, dateChanged DESC, totalComments DESC