mysql query on equal values in a column

Question

I have the following table

| id | title    | parent |
------------------------
|  1 | example  |   0    |
|  2 | example2 |   2    |
   3   example3     3
   4   example4     2
   5   example5     2

How can I make a query in function of title field to get back all the row where parent_id's values are equal.

example: show all rows having the same parent as the row which matches title="example2".

should give back

2 | example2 | 2
4 | example4 | 2
5 | example5 | 2

if title="example2" then why do u want id 4 and 5 ??

sujal
– sujal

2012-05-02 12:11:08 +00:00
Commented May 2, 2012 at 12:11 — sujal
– sujal, Commented May 2, 2012 at 12:11
@sujal because they're related to the same parent

Kaii
– Kaii

2012-05-02 12:18:11 +00:00
Commented May 2, 2012 at 12:18 — Kaii
– Kaii, Commented May 2, 2012 at 12:18

Kaii · Accepted Answer · 2012-05-02 12:29:44Z

1

When you only have the title as argument and want all the rows that are related to the same parent_id you may utilize a subquery:

SELECT * FROM tbl WHERE parent IN (SELECT parent FROM tbl WHERE title = "example2")

or you can basically achieve the same using a self-join:

SELECT related.* 
FROM tbl source
LEFT JOIN tbl related ON source.parent = related.parent
WHERE source.title = "example2"

edited May 2, 2012 at 12:29

answered May 2, 2012 at 12:11

Kaii

20.6k3 gold badges41 silver badges63 bronze badges

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

Comments

Darren · Accepted Answer · 2012-05-02 12:08:48Z

0

Your question is a bit confusing:

If you want the results where parent is 2 then use (As mattytommo suggests):

 SELECT * FROM table
 WHERE parent = 2

If you want the results where the parent is equal to id then use:

SELECT * FROM table
WHERE id = parent

answered May 2, 2012 at 12:08

Darren

71.2k24 gold badges141 silver badges146 bronze badges

Comments

Bohemian · Accepted Answer · 2012-05-02 12:10:52Z

0

I think you are trying to say "give me all rows that have the same parent id as my matching row", which would be this query:

select t2.*
from table t1
join table t2 on t2.parent = t1.parent
where t1.title = 'example2'

answered May 2, 2012 at 12:10

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

1 Comment

Kaii Over a year ago

i guess t1 and t2 should be aliases to the same table (self-join).. if so, you should declare them as such (or add a view 't2' to the database that reflects SELECT * FROM t1)

Collectives™ on Stack Overflow

mysql query on equal values in a column

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related