Join tables to display activity

[SimonIoa]

Hello i have a database which users make groups or send messages to other registered, in our project, users. So you have messages as M,users as U, groups as G. So you want to show all updates that happen in these tables WHERE (uid - thats the user id column on U table.). All tables have CREATED as datetime column. Users yable is uid on messages is uid_fk and to groups uid_fk. Can i do that with a single statement or do i need to do anything else?

[SimonIoa]

something like this is the solution.

            (SELECT DISTINCT M.msg_id as msg_id, U.username,S.uid_fk, S.ouid_fk as ouid_fk , M.group_id_fk,M.message, S.created, '0' as type, '' as group_name
FROM
messages M, users U,message_share S
WHERE
U.status='1' AND
M.uid_fk = S.ouid_fk AND
S.msg_id_fk = M.msg_id AND S.uid_fk<>'99' GROUP BY M.msg_id)

UNION
(SELECT
DISTINCT '0' as msg_id,U.username, X.uid_fk as uid_fk, '0' as ouid_fk , X.group_id_fk as group_id_fk, '0' as message, X.created, '4' as type, G.group_name as group_name
FROM users U, groups G, group_users X
WHERE G.uid_fk=U.uid AND G.group_id=X.group_id_fk AND U.uid='99' AND X.uid_fk<>'99' AND X.status='1' AND U.status='1')

UNION
   (SELECT
DISTINCT M.msg_id as msg_id, U.username,M.uid_fk as uid_fk, '0' as ouid_fk , M.group_id_fk as group_id_fk, M.message as message, M.created, '5' as type, '' as group_name
FROM users U, messages M,group_users G
WHERE G.uid_fk=U.uid AND G.group_id_fk=M.group_id_fk AND U.uid='99' AND M.uid_fk<>G.uid_fk AND G.status='1' AND U.status='1')

UNION
(SELECT DISTINCT M.msg_id  as msg_id, U.username,S.uid_fk, S.ouid_fk as ouid_fk, M.group_id_fk,M.message, S.created, '1' as type, '' as group_name
FROM
messages M, users U,message_points S
WHERE
U.status='1' AND
M.uid_fk = S.ouid_fk  AND
S.msg_id_fk = M.msg_id AND S.uid_fk<>S.ouid_fk AND S.uid_fk<>'99' GROUP BY M.msg_id)

UNION
(SELECT DISTINCT M.msg_id as msg_id,U.username, S.uid_fk, M.uid_fk as ouid_fk, M.group_id_fk,M.message, S.created, '2' as type, '' as group_name
FROM
messages M, users U,comments S
WHERE
U.status='1' AND
M.uid_fk ='99' AND
S.msg_id_fk = M.msg_id  AND S.uid_fk<>'99'  GROUP BY M.msg_id)
ORDER BY created DESC

[SeanDevoy]

Hi Simonloa,
I have years and years of sql server experience. I would like to help you but it is hard to follow what your tables are and what the goal is. Are you running these queries from an application? If so, what language?

In general UNION is a very inefficient query operator, but is required in some cases. I suspect in this case the results could be achieved with something like " where (x and y and z) or (x1 and y1 and z) or (x and y2 and z)" etc.

Perhaps you could post sample table data for each table(4 or 5 rows in each and ALL columns) and the result set you would like to see.

Sean