mysql join on same table with different ID's

Question

Table : datatable

    +----+-------+-------+
    | Id | Name  | Value |
    +----+-------+-------+
    | 50 | Eric  | 1200  |
    | 50 | Barb  | 1195  |
    | 50 | Joe   | 1180  |
    | 51 | Barb  |   6   |
    | 51 | Eric  |   3   |
    | 51 | Joe   |   5   |
    +----+-------+-------+

I want to this result

    +-------+---------+--------+
    | Name  | Value1  | Value2 |
    +-------+---------+--------+
    | Eric  |  1200   |   3    |
    | Barb  |  1195   |   6    |
    | joe   |  1180   |   5    |
    +-------+---------+--------+

I don't know how to merge this.

SELECT name, value from datatable WHERE Id=50 AS Value1 
JOIN datatable 
WHERE Id=51 AS  Value2 ON value1.Name = value2.Name ORDER BY value1.Value

and dont know how to call value2?

$row["??"]

Seriously consider handling issues of data display in application code — Strawberry
– Strawberry, Commented Nov 1, 2018 at 12:04

Madhur Bhaiya · Accepted Answer · 2018-11-01 11:54:13Z

1

You can do a Group By on Name.
Now, you can utilize conditional Aggregation using Case .. When and Max() functions.

Try the following query:

SELECT 
  t.Name, 
  MAX(CASE WHEN t.Id = 50 THEN t.VALUE END) AS Value1,
  MAX(CASE WHEN t.Id = 51 THEN t.VALUE END) AS Value2 
FROM datatable AS t
GROUP BY t.Name

answered Nov 1, 2018 at 11:54

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

3 Comments

user2796291 Over a year ago

it works but shows empty cells, i figured out in php -- if (Value1 != null) it is working but not the best way i think. Thank you so much.

Madhur Bhaiya Over a year ago

@user2796291 if there is no value for id = 50 or 51, what do you want to show then ?

user2796291 Over a year ago

that code merge 50 and 51, its ok. but it list all other IDs. 41,42, etc...

kkica · Accepted Answer · 2018-11-01 15:47:28Z

0

Use table aliases

SELECT t1.name, t1.value AS Value1,t2.value AS Value2 from datatable t1 
JOIN datatable t2 ON t1.Name = t2.Name
WHERE t1.Id=51 and t2.Id=50 
ORDER BY value1.Value

edited Nov 1, 2018 at 15:47

answered Nov 1, 2018 at 11:55

kkica

4,1041 gold badge22 silver badges41 bronze badges

1 Comment

user2796291 Over a year ago

MySQL ERROR : #1052 - Column 'name' in field list is ambiguous

Collectives™ on Stack Overflow

mysql join on same table with different ID's

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related