MySQL select row if it's specified in another row

Question

I have one table with multiple rows where some rows are the other's "children".

The table looks like this:

id, name, parent, slug

Sample data:

1, Jack, NULL, jack 
2, John, NULL, john 
3, Mike, jack, mike

The query should return jack, because it's mike's parent.

so I want to return the rows that are specified in other rows as "parents". I'm really new to MySQL and I have no idea how to do it.

Unfortunately I didn't get lucky with Google too because I'm not sure how to ask exactly.

Thanks!

Please edit your question and add sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Jan 23, 2015 at 17:02
This is a really bad table design - the parent should be the id field, not the name. — Siyual
– Siyual, Commented Jan 23, 2015 at 17:06
The table is a bit more complex, but I made it simpler here so you can understand my question easier. — Alex
– Alex, Commented Jan 23, 2015 at 17:07
@Alex Please provide the actual table structure. Is Parent actually referencing the name field, as you've defined, or is it actually referencing the id field? If it's using the Name field, you will definitely want to consider refactoring this table. — Siyual
– Siyual, Commented Jan 23, 2015 at 17:09
@Alex Yes, you should use the ID for the parent reference. That's the identifying field for the record. Consider: what would happen if there were two people named Jack? — Siyual
– Siyual, Commented Jan 23, 2015 at 17:13

Gordon Linoff · Accepted Answer · 2015-01-23 17:12:05Z

1

If you want rows that have children:

select distinct p.*
from table p join
     table c
     on p.name = c.parent;

Or, similarly:

select p.*
from table p
where exists (select 1 from table c where c.parent = p.name);

Note: table means put in your table there.

answered Jan 23, 2015 at 17:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oleg Dubas · Accepted Answer · 2015-01-23 17:13:19Z

1

You need something like this:

SELECT * FROM table_name AS t1 WHERE 
    (SELECT COUNT(*) FORM table_name AS t2 WHERE t2.parent = t1.name) > 0

OR

SELECT t1.* FROM table_name AS t1 
    LEFT JOIN table_name AS t2 ON t2.parent = t1.name
    GROUP BY t1.id

edited Jan 23, 2015 at 17:13

answered Jan 23, 2015 at 17:05

Oleg Dubas

2,3481 gold badge12 silver badges24 bronze badges

2 Comments

Alex Over a year ago

I don't want to get the children, but the parents (rows that have children).

Oleg Dubas Over a year ago

Updated, try this one

Alisa · Accepted Answer · 2015-01-23 17:24:26Z

1

select parent_person.* 
from person
    inner join person as parent_person on parent_person.name = person.parent

answered Jan 23, 2015 at 17:24

Alisa

3,1423 gold badges35 silver badges45 bronze badges

Collectives™ on Stack Overflow

MySQL select row if it's specified in another row

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related