I have a table containing the following data:
+-----------------+--------------+----------------------+------------+--------------------+-----------+------+---------+---------+-----+------------------------+---------------------+
| notification_id | from_user_id | from_user_auth_level | to_user_id | to_user_auth_level | status_id | type | subject | message | url | timestamp_inserted_utc | timestamp_read_utc |
+-----------------+--------------+----------------------+------------+--------------------+-----------+------+---------+---------+-----+------------------------+---------------------+
| 1 | NULL | NULL | 1 | 1 | 0 | 2 | test | test | url | 2010-10-10 00:00:00 | 2011-10-10 00:00:00 |
| 2 | 2 | 5 | 1 | 1 | 0 | 2 | test | test | url | 2010-10-10 00:00:00 | 2011-10-10 00:00:00 |
| 3 | 3 | 5 | 1 | 1 | 0 | 2 | test | test | url | 2010-10-10 00:00:00 | 2011-10-10 00:00:00 |
| 4 | 2295 | 4 | 1 | 1 | 0 | 2 | test | test | url | 2010-10-10 00:00:00 | 2011-10-10 00:00:00 |
| 5 | 10 | 1 | 1 | 1 | 0 | 2 | test | test | url | 2010-10-10 00:00:00 | 2011-10-10 00:00:00 |
+-----------------+--------------+----------------------+------------+--------------------+-----------+------+---------+---------+-----+------------------------+---------------------+
And then I have some other tables like 'users', 'companies', 'organizations', ... etc.
I need to be able to get the username, gender and image of every notification (based on the from_user_id and from_user_auth_level).
But the problem resides in the fact, that this info resides in different places, depending on what the user_auth_level is.
For example: if my user is a "regular" user, his auth_level will be 1. And the image will reside in my "users" table, and the gender is applicable. But if the user has auth_level == 5, it means he is an organization. In this case, gender is not applicable, and the image resides in the "organization" table, this needs to be linked via users to user_roles and then to the organization.
And this goes on for every user type, they all require different joins.
I have created a working query, but this uses UNION's everywhere, and I have read that it is not the best to use for performance reasons, so i'm hoping someone can guide me to improving this query with performance in mind:
SELECT n.*, NULL as username, NULL as gender, NULL as picture
FROM notification as n
WHERE n.from_user_auth_level IS NULL
AND n.to_user_id = $userid
UNION
SELECT n.*, u.username, u.gender as gender, u.profile_picture as picture
FROM notification as n
LEFT JOIN users AS u ON n.from_user_id = u.user_id
WHERE n.from_user_auth_level = 1
AND n.to_user_id = $userid
UNION
SELECT n.*, u.username, NULL as gender, c.logo as picture
FROM notification as n
LEFT JOIN users AS u ON n.from_user_id = u.user_id
LEFT JOIN user_companies AS uc on u.user_id = uc.user_id
LEFT JOIN company as c on uc.company_id = c.company_id
WHERE n.from_user_auth_level = 4
AND n.to_user_id = $userid
UNION
SELECT n.*, u.username, NULL as gender, o.logo as picture
FROM notification as n
LEFT JOIN users AS u ON n.from_user_id = u.user_id
LEFT JOIN user_roles as ur on u.user_id = ur.user_id
LEFT JOIN organization as o on ur.org_id = o.org_id
WHERE n.from_user_auth_level = 5
AND n.to_user_id = $userid
UNION
SELECT n.*, u.username, NULL as gender, o.logo as picture
FROM notification as n
LEFT JOIN users AS u ON n.from_user_id = u.user_id
LEFT JOIN user_roles as ur on u.user_id = ur.user_id
LEFT JOIN organization as o on ur.org_id = o.org_id
WHERE n.from_user_auth_level = 7
AND n.to_user_id = $userid
UNION
SELECT n.*, u.username, NULL as gender, NULL as picture
FROM notification as n
LEFT JOIN users AS u ON n.from_user_id = u.user_id
WHERE n.from_user_auth_level = 9
AND n.to_user_id = $userid"
After I get this result, I use PHP to order the results based on the timestamp_inserted_utc, since it's not possible to get the correct results with the UNION.
I'd use the notification table as base and used conditional outer joins as:
select
n.*,t1.gender, t2.orgNo
from
notifications n
left outer join table1 t1 on (n.auth=1 and more join)
left outer join table2 t2 on (n.auth=2 and more..)
You will have more columns but their name would make sense and you may merge at application level.