Four left joins, having...

[minitauros]

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.

[smantscheff]

What do you mean by "does not work" ?
Show the relevant table structure.

How about this:

select 
count(*) from persons where user_id=@super
+ count(*) from companies where user_id=@super
+ count(*) from artists where user_id=@super
+ count(*) from events where user_id=@super

[minitauros]

The table structure is like this:

Each record in the persons, artists, companies and events table is a profile. User_id is the id of the superuser that is the owner of the profile.

Table "connections" looks like this:

connect_from_type,
connect_from_id,
connect_to_type,
connect_to_id

I guess you can guess what each of those fields means.

The suggestion you did would count all the profiles, while I would like to count the connection requests made to all the profiles of one specific user. Does that clear things up? :) Thanks for the help in advance!

P.S. By "does not work" I mean that one option returns 0 results, and the other option returns too many results (for example it returns 14 connections when there are actually 6 connections).

[smantscheff]

Without a test case I'm not sure what you mean.
It looks like bad table design. Can you change it?

Can you do it with 4 summands?

(select count(*) from connections c, persons a where c.connect_to_id = a.id and c.connection_type = 1 and a.user_id=@super)
+ (select count(*) from connections c, artists a where c.connect_to_id = a.id and c.connection_type = 2 and a.user_id=@super)
+ (select count(*) from connections c, companies a where c.connect_to_id = a.id and c.connection_type = 3 and a.user_id=@super)
+ (select count(*) from connections c, events a where c.connect_to_id = a.id and c.connection_type = 4 and a.user_id=@super)