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

In one table I store events (like parties).

Example:

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

Example:

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.

CodePudding user response：

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.