CODEWITHSUNDEEP
phpmysqlsqlinner-join
stack
stack

Reputation: 10228

How to get related row from same table?

I have a table like this:

// mytable
+----+---------+---------+
| id | related |  color  |
+----+---------+---------+
| 1  | 1       | red     |
| 2  | 1       | blue    |
| 3  | 3       | green   |
| 4  | 1       | white   |
| 5  | 3       | brown   |
| 6  | 6       | gray    |
| 7  | 3       | black   |
| 8  | 1       | orange  |
| 9  | 6       | pink    |
+----+---------+---------+

I have an id number and I need to get the color of related id.

For example:

$id = 4; // I need to get `red`
$id = 5; // I need to get `green`
$id = 6; // I need to get `gray`
$id = 9; // I need to get `gray`

I can do that by using a JOIN. Something like this:

SELECT t2.color FROM mytable t1 JOIN mytable t2 ON t1.related = t2.id WHERE t1.id = :id

My query works as expected .. But I'm not sure using a JOIN for doing that is standard. Actually I'm trying to know is there any better approach? Or mine is a normal way?

Upvotes: 1

Views: 79

Answers (3)

nullcall
nullcall

Reputation: 1

you can handle this in simple ways also 

select t.color from mytable t where t.id = '$id' (for one value)
select t.color from mytable t where t.id in ('$id1','$id2','$id3','$id4' ) (for multi-values comma separated strings)

Upvotes: 0

Blank
Blank

Reputation: 12378

I've done two different query and explain them, hope can give you some hints.

SQL Fiddle

MySQL 5.6 Schema:

CREATE TABLE mytable
    (`id` int, `related` int, `color` varchar(6))
;

INSERT INTO mytable
    (`id`, `related`, `color`)
VALUES
    (1, 1, 'red'),
    (2, 1, 'blue'),
    (3, 3, 'green'),
    (4, 1, 'white'),
    (5, 3, 'brown'),
    (6, 6, 'gray'),
    (7, 3, 'black'),
    (8, 1, 'orange'),
    (9, 6, 'pink')
;

Query 1:

SELECT t2.color FROM mytable t1 JOIN mytable t2 ON t1.related = t2.id WHERE t1.id = '4'

Results:

| color |
|-------|
|   red |

Query 2:

explain SELECT t2.color FROM mytable t1 JOIN mytable t2 ON t1.related = t2.id WHERE t1.id = '4'

Results:

| id | select_type | table | type | possible_keys |    key | key_len |    ref | rows |                                              Extra |
|----|-------------|-------|------|---------------|--------|---------|--------|------|----------------------------------------------------|
|  1 |      SIMPLE |    t1 |  ALL |        (null) | (null) |  (null) | (null) |    9 |                                        Using where |
|  1 |      SIMPLE |    t2 |  ALL |        (null) | (null) |  (null) | (null) |    9 | Using where; Using join buffer (Block Nested Loop) |

Query 3:

SELECT t1.color FROM mytable t1 WHERE exists (select 1 from mytable t2 where t1.id =  t2.related and t2.id ='4')

Results:

| color |
|-------|
|   red |

Query 4:

explain SELECT t1.color FROM mytable t1 WHERE exists (select 1 from mytable t2 where t1.id =  t2.related and t2.id ='4')

Results:

| id |        select_type | table | type | possible_keys |    key | key_len |    ref | rows |       Extra |
|----|--------------------|-------|------|---------------|--------|---------|--------|------|-------------|
|  1 |            PRIMARY |    t1 |  ALL |        (null) | (null) |  (null) | (null) |    9 | Using where |
|  2 | DEPENDENT SUBQUERY |    t2 |  ALL |        (null) | (null) |  (null) | (null) |    9 | Using where |

Upvotes: 2

NLink
NLink

Reputation: 485

What's wrong with SELECT t.related FROM mytable t WHERE t.id = :id? JOIN makes nothing more but checking if there is a actual id in 'related' column or not

Upvotes: 2

Related Questions