CODEWITHSUNDEEP
mysqlsqlcountwhere-clausecorrelated-subquery
innom
innom

Reputation: 1007

Join two tables and get count of one but all results matching ID on other

This sounds a bit confusing, but it's actually easy.

In one table I store events (like parties).

Example:

USERID EventName EVENTID
10 CoolEvent 0
10 MediocreEvent 1

And another table stores invites to that party. Those can have a different status, like "accepted" or "host" (or "declined" and so on).

Example:

EVENTID status
0 accepted
0 host
0 declined

Basically, I need to get all events from one USERID, but also I want to know how many people have either accepted to that event or are the host.

So if I want to find out what events the user with the ID 10 has the result should be:

{EVENTID: 0, EventName: CoolEvent, Count(status == accepted OR host): 2}

{EVENTID: 1, EventName: MediocreEvent, Count(status == accepted OR host): 0}

So two results in total.

This has to happen in one single query.

Can you help me here?

I tried:

var sqlString = "SELECT a.name, a.venueAdresse, a.userID, a.tblEventID, a.venueCity, a.startTimeAndDateUnixTicks, a.displayStartTime, a.slots, a.eventPictureThumb, COUNT(b.status) \
FROM 10561_12865_tblEvents a \
RIGHT JOIN 10561_12865_tblInvites b ON (a.tblEventID = b.tblEventID) \
WHERE a.userID  = '" + userID + "'\
AND (b.status = 'accepted' OR b.status = 'host') \
ORDER BY createdUnixInt DESC \
LIMIT " + start + "," + count

But this will only return results where ID matches AND status is either accepted or host, but I just want to count the occurrences from one table. So basically the AND just refers to the count property.

So I am a bit stuck here.

Upvotes: 2

Views: 1302

Answers (2)

Shakib Rahman
Shakib Rahman

Reputation: 1

SET sql_mode = ( SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '') );

SELECT t.EventName, t.EVENTID, t.Status, count(Status) FROM (SELECT e.EventName, e.EVENTID, i.Status FROM event_table e LEFT JOIN invite_table i ON e.EVENTID = i.EVENTID) AS t GROUP BY t.EVENTID, t.Status

Upvotes: 0

forpas
forpas

Reputation: 164089

The simplest way is with a correlated subquery:

SELECT e.name, e.venueAdresse, e.userID, e.tblEventID, e.venueCity, e.startTimeAndDateUnixTicks, e.displayStartTime, e.slots, e.eventPictureThumb, 
       (
         SELECT COUNT(*) 
         FROM `10561_12865_tblInvites` i
         WHERE i.tblEventID = e.tblEventID AND (i.status = 'accepted' OR i.status = 'host')
       ) counter
FROM `10561_12865_tblEvents` e 
WHERE e.userID  = ? 
ORDER BY e.createdUnixInt DESC 
LIMIT ?

Replace the ? placeholders with the parameters that you pass.

Upvotes: 2

Related Questions