Photoshop Question posted in
The official user guide can be found here.

Alternative to GROUP CONCAT – Photoshop

I stumbled upon this issue and can’t seem to get the hang of it.
I have three tables:

MSG_V3_THREAD

enter image description here

MSG_V3_THREAD_USERS

enter image description here

MSG_V3_THREAD_PARTNERS

enter image description here

Now, what I need to do is to get this table in the end:

enter image description here

(NOTE: the above image is photoshopped)

I cannot seem to find a solution to this. I managed to do a group_concat which gives me the correct result but it is not in the format I need. I also have this query at the moment:

SELECT  msg_v3_thread.thread_id, user_id, NULL as partner_id
FROM msg_v3_thread
LEFT JOIN msg_v3_thread_users ON msg_v3_thread_users.thread_id = msg_v3_thread.thread_id 
WHERE msg_v3_thread.thread_id = 3
UNION
SELECT  msg_v3_thread.thread_id, partner_id, NULL as user_id
FROM msg_v3_thread
LEFT JOIN msg_v3_thread_partners ON msg_v3_thread_partners.thread_id = msg_v3_thread.thread_id 
WHERE msg_v3_thread.thread_id = 3

Which results into this:

enter image description here

It just puts all the ids in one column.

Could anyone please help me with this? It might be that the solution is pretty simple but I just can’t see it.

Thank you!

Tags:

2

Answers


  1. 0

    You had the right idea, you just need to switch around the explicit nulls you’re querying. Aliases are ignored in all the queries except the first when you use the union operator:

    SELECT  msg_v3_thread.thread_id, user_id, NULL as partner_id
FROM msg_v3_thread
LEFT JOIN msg_v3_thread_users ON msg_v3_thread_users.thread_id = msg_v3_thread.thread_id 
WHERE msg_v3_thread.thread_id = 3
UNION
SELECT  msg_v3_thread.thread_id, NULL as user_id, partner_id -- Here!
FROM msg_v3_thread
LEFT JOIN msg_v3_thread_partners ON msg_v3_thread_partners.thread_id = msg_v3_thread.thread_id 
WHERE msg_v3_thread.thread_id = 3
    Login or Signup to reply.
  2. 0

    I think you just want union like this:

    select thread_id, user_id, NULL as partner_id
from MSG_V3_THREAD_USERS
where thread_id = 3
union
select thread_id, NULL, partner_id
from MSG_V3_THREAD_PARTNERS
where thread_id = 3;

    Note that the union removes duplicates both within tables and between them. You could also write this as:

    select distinct thread_id, user_id, NULL as partner_id
from MSG_V3_THREAD_USERS
where thread_id = 3
union all
select distinct thread_id, NULL, partner_id
from MSG_V3_THREAD_PARTNERS
where thread_id = 3;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search