The case:
On my site there are four profile types: persons, artists, companies and events. A user can create multiple profiles. Each profile can be connected to another profile. So a person-profile can for example connect to an artist-profile, but also to an event-profile, and an event-profile can connect to a person-profile, but also to a company-profile or an artist-profile, etc.
I want to count the number of connection requests that have been made to a certain user with a certain super_id ($user_id in the query below). The super_id defines the owner of the person, artist, company or event profile.
Shortly explained: I want to count the connection requests that are made to all the profiles of a certain user with a certain super_id, expressed as $user_id in the query below.
SELECT COUNT(connections.connect_to_id) AS number_of_requests,
CASE connections.connect_to_type
WHEN 1 THEN persons.user_id
WHEN 2 THEN artists.user_id
WHEN 3 THEN companies.user_id
WHEN 4 THEN events.user_id
END AS owner_id
FROM connections
LEFT JOIN persons ON connections.connect_to_id = persons.id
LEFT JOIN artists ON connections.connect_to_id = artists.id
LEFT JOIN companies ON connections.connect_to_id = companies.id
LEFT JOIN events ON connections.connect_to_id = events.id
WHERE connections.status = 0
AND (persons.user_id = "' . $user_id . '"
OR artists.user_id = "' . $user_id . '"
OR companies.user_id = "' . $user_id . '"
OR events.user_id = "' . $user_id . '")
HAVING owner_id = "' . $user_id . '"
But this code does not work. I have also tried leaving the "HAVING ..." part out - no succes - and leaving the "AND ( ... )" part out - also no success.
Can anyone please help me with this? It would be very much appreciated :). I was thinking of writing some kind of IF { } ELSE { } in the WHERE clause, but for as far as I understand, that's not really possible in MySQL.