Reputation: 2804
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
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.
Upvotes: 1
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
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