CODEWITHSUNDEEP
mysqlsqljoinunion
Helge Talvik Söderström
Helge Talvik Söderström

Reputation: 2804

MySQL join vs union

I have a single table, message, with the columns id (primary key), parent_id (foreign key to self), owner_id (creator user ID) and message (the actual message).

I'm now trying to retrieve all columns that are either owned by a specific owner_id or that have a parent_id pointing to another record which is owned by a specific owner_id.

I've successfully done this thus:

select * from message m1
left join message m2 on m1.id = m2.parent_id
where m1.owner_id = 1;

However, I get the result as this (m2 columns added on the right side):

id  parent_id   owner_id    message         id      parent_id   owner_id    message
------------------------------------------------------------------------------------------
1   NULL        1           First message   3       1           2           Third message
1   NULL        1           First message   4       1           2           Fourth message
2   NULL        1           Second message  NULL    NULL        NULL        NULL

... when I want it like this (a simple list of all unique matching columns, order is unimportant):

id  parent_id   owner_id    message
------------------------------------------
1   NULL        1           First message
3   1           2           Third message
4   1           2           Fourth message
2   NULL        1           Second message

I realize I could do this with a union, but I can't see any way of designing a union query without making it tremendously inefficient.

How would you solve such a problem?

Thanks.

EDIT:

Here's the table I'm working with:

create table message (
   id int(11) unsigned auto_increment primary key,
   parent_id int(11) unsigned default null,
   owner_id int(11) unsigned not null,
   message varchar(255) default null,

   index (parent_id),

   foreign key (parent_id) references message(id) on update cascade on delete cascade
) engine=innodb default charset=utf8;

Upvotes: 0

Views: 289

Answers (3)

xiaofeng.li
xiaofeng.li

Reputation: 8597

Left join and a literal translation from the requirement will do.

select m.* from message m 
left join message p on m.parent_id=p.id
where m.owner_id=1 or p.owner_id=1
order by m.id

The above SQL selects messages whose owner id is 1 or whose parent's owner id is 1.

SQLFiddle Example

Upvotes: 1

Allan
Allan

Reputation: 12456

You can go for the following query with a union:

select id from message where owner_id="USER" --all the records for which the owner is directly "USER"
union all -- to remove duplicates generated by the union if any
select m1.id from message m1, message m2 where m1.parent_id=m2.parent_id and m1.id != m2.id and m2.owner_id="USER"
-- all the records from m2 that share the same parent_id as m1 (but that are not the same id to avoid taking the m1 record itself)
-- for which m2.owner is the specified one

Good luck! Cheers

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

If I understand correctly, you want any message where either the parent or the owner is 1. You then want to order this hierarchically. Because you are looking for only a single id, I think this does what you want:

select m.*
from message m
where 1 in (m.id, m.parent_id)
order by coalesce(m.parent_id, m.id), id;

Upvotes: 0

Related Questions