CODEWITHSUNDEEP
sqljoin
YuChun Lin
YuChun Lin

Reputation: 23

SQL query about Nested query and NATURAL JOIN

I have three tables. First table is Activities and second is Members and last is Message. Activities has a foreign key named Members_ID and primary key is Activity_ID. Members' primary key is Members_ID. Message recorded Activity_ID and Members_ID. I wanna search about Activities NATURAL JOIN Members and I need a new column count for Message's message_ID where the Activity_ID is the same.

NATURAL JOIN:

SELECT*
    FROM Activities 
        NATURAL JOIN Members 
    WHERE Activities.Members_ID = Members.Members_ID;

COUNT(message_ID):

SELECT COUNT(message_ID) 
    FROM Message 
    WHERE Activity_ID = 123;

Question is: How to search the above two things together? I'd like have a table have Activities NATURAL JOIN Members with same Activities.Members_ID and a virtual column which is COUNT(message_ID) from table Message.

Upvotes: 1

Views: 3339

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

WITH MessageTallies 
     AS
     (
      SELECT Activity_ID, COUNT(message_ID) AS tally
        FROM Message
       GROUP 
          BY Activity_ID
     )
SELECT *
  FROM Activities 
       NATURAL JOIN Members 
       NATURAL JOIN MessageTallies
UNION CORRESPONDING
SELECT *, 0 AS tally
  FROM Activities 
       NATURAL JOIN Members 
 WHERE Activity_ID NOT IN (SELECT Activity_ID FROM MessageTallies):

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

SELECT a.Activity_ID, a.col1, a.col2, ..., a.colx, COUNT(mg.message_ID)
    FROM Activities a
        INNER JOIN Members m
            ON a.Members_ID = m.Members_ID
        INNER JOIN Message mg
            ON a.Activity_id = m.Activity_ID 
    GROUP BY a.Activity_ID, a.col1, a.col2, ..., a.colx;

Upvotes: 0

Tim Lehner
Tim Lehner

Reputation: 15251

Perhaps you wish to use a correlated subquery, like this:

SELECT *, (SELECT COUNT(message_ID) FROM Message WHERE Activity_ID = a.Activity_ID AND Members_ID = m.Members_ID) AS Message_Count
    FROM Activities AS a
        NATURAL JOIN Members AS m
    WHERE a.Members_ID = m.Members_ID;

But, I would avoid the natural join syntax:

SELECT *, (SELECT COUNT(message_ID) FROM Message WHERE Activity_ID = a.Activity_ID AND Members_ID = m.Members_ID) AS Message_Count
    FROM Activities AS a
        JOIN Members AS m on a.Members_ID = m.Members_ID;

Upvotes: 0

Related Questions