mysql select rows including related

Question

I have table:

id | parent | regno | person
1  | 0      | 12    | 5
2  | 1      | 12    | 15
3  | 0      | 13    | 5
4  | 0      | 14    | 6

I have MySQL query...

SELECT * 
  FROM table 
 WHERE person='5';

...that returns rows 1 and 3.

In this table row 1 and 2 are related (same regno).

How can i build this query to include related rows?

Basically when searching for person 5 i need MySQL query to return following:

id | parent | regno | person
1  | 0      | 12    | 5
2  | 1      | 12    | 15
3  | 0      | 13    | 5

Parent column has id of column it is related to, but it can be positive and negative integer. All related rows always have same regno.

Thank you.

Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Madhur Bhaiya, Nov 02 '18 at 11:33
yes only one level deep, but there might be more than one related. In this example there can be more rows with regno 12. — DjBacon, Nov 02 '18 at 11:33
forgot to mention that "parent" might be with negative sign, like "-1". — DjBacon, Nov 02 '18 at 11:36

Madhur Bhaiya · Answer 1 · 2018-11-02T11:58:46.673

0

You can get all the id values for the person = '5' in a Derived Table.
Now, join back to the main table, matching either the absolute of parent (to get the child row(s)) or the id (to get the parent id row itself).

Based on discussion in comments, Try:

SELECT t.* 
FROM your_table AS t 
JOIN 
(
  SELECT id AS parent_id  
  FROM your_table 
  WHERE person = '5'
) AS dt 
  ON dt.parent_id = ABS(t.parent) OR 
     dt.parent_id = t.id

It is hard to comprehend though, why would you put negative values in parent!

edited Nov 02 '18 at 11:58

answered Nov 02 '18 at 11:36

Madhur Bhaiya

28,155
10
49
57

"parent" is negative because there are two types of children and since it is already huge table i did not want to create another column so i just define one type as positive and the other one as negative. – DjBacon Nov 02 '18 at 15:48
i haven't got to it yet. i had one more urgent thing to do and on Monday hopefully i will return to this problem. don't worry, if your solution will work for me i will mark this as accepted answer. – DjBacon Nov 03 '18 at 15:24
Thank you for your help. Since it was easier for me to implement Caius Jard's suggested solution with "IN" I marked his solution as accepted. – DjBacon Nov 05 '18 at 09:19
@DjBacon no worries. When the data becomes very big and you start having performance issues, you can come back to using this "more efficient" solution :) – Madhur Bhaiya Nov 05 '18 at 09:20

Caius Jard · Accepted Answer · 2018-11-02T12:06:57.287

You want all people who have a regno that is the same as the regno of anyone who is person 5:

--this main query finds all people with the regno from the subquery
SELECT * 
FROM table
WHERE regno IN 
( --this subquery finds the list of regno
  SELECT regno
  FROM table 
  WHERE person = '5'
)

There are other ways to write this; i'm not a fan of IN, and personally would write it like this:

SELECT t.* 
FROM table t
INNER JOIN
(
  SELECT DISTINCT regno
  FROM table 
  WHERE person = '5'
) u
WHERE t.regno = u.regno

But it's harder to understand, and it's quite likely that these queries would end up being executed identically internally anyway. In this form the DISTINCT is required to make the regno from the subquery unique. If it were not, joined rows would end up duplicated. Why do I prefer it over IN? In some database systems IN's implementation can be very naive and low performing. "Never use IN to create a list longer then you would write by hand" is an old mantra I tend to stick to. This join pattern is also more flexible, can work with multiple values. Not every database supports Oracle-esque where x,y in ((1,3),(3,4)) value multiples

As an aside (and partly in response to the first comment on this answer) it would be more typical and more useful/usual to have the database prepare a set of rows that had parent and child data on the same line

It would look more like this:

SELECT * 
FROM 
table c
LEFT OUTER JOIN
table p
ON c.regno = p.regno AND p.parent = 1
WHERE c.person = '5' AND c.parent=0

This is assuming your "parent" column is 0 1 indicating true false.. you seem to have made a comment that parent is the id of the relative (not sure if it's parent-of or parent-is)

For a table where there is an id, and parentid column, and the parentid is set to a value when the row is a child of that other id;

id, parentid, name
1, null, Daddy
2, 1, Little Jonny
3, 1, Little Sarah

That looks like:

SELECT * FROM table c INNER JOIN table p ON c.parentid = p.id WHERE p.parentid ID NULL

Rows can have only one parent. A NULL in the parent id defines the row as being a parent, otherwise it's a child. You could turn this logic on its head if you wanted, call the column isparentof and have all child rows with null in the isparentof, and anyone who is a parent of a child, out the child id in isparentof. This then limits you to one child per multiple parents (single child families).. the query to pull them out is broadly the same

@DjBacon I think the first query in this answer should work. However, your question is ambiguous; please add some details to the question. What is the relevance of `parent` field ? — Madhur Bhaiya, Nov 02 '18 at 11:46
parent shows id of row it is related to, but can be positive and negative integer. anyway it's absolute value is id of row it is related to. — DjBacon, Nov 02 '18 at 11:50
thank you, i will try to implement this in my real query. i got your the idea. thank you. — DjBacon, Nov 02 '18 at 11:55
Thank you again for your solution. Since i have huge table and already multiple joins i went with IN and it worked like a charm. — DjBacon, Nov 05 '18 at 09:16

mysql select rows including related

2 Answers2