Question 20560 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL SELECT one row plus another one column of another row in the same table

I have a table looking like this

Table: Stores
| Group  | ID    | Name    |  Link     |
| 1      | 1     | Store A |           |
| 1      | 2     | Store B |   3       |
| 1      | 3     | Store C |           |
| 2      | 4     | Store D |           |

So, what I want to do is that I want to do a query which is showing as a result the ID and Name of the queried row plus, in case there is a ID mentioned in column "Link", I want to see a new column with the name of the ID where the link refers to.
By this the output of queriying ID 2 should look like:

| ID    | Name    |  Link     |
| 2     | Store B |  Store C  |

I tried it with the following query, but the name of the link is always returned as NULL.

SELECT `ID`, `Name`,(SELECT `Name` FROM `Stores` WHERE Stores.ID = Stores.Link ) AS Link FROM `Stores` WHERE (`ID` LIKE '2');

Is it possible to query a table in this way?

Tags:

3

Answers


  1. 0

    You can left join the same table again with a different alias name

    SELECT s.id, s.name, l.name AS linkname
FROM Stores s
LEFT JOIN Stores l ON s.link = l.id
WHERE s.id = 2
    Login or Signup to reply.
  2. 0

    If you use the MySQL default (inner) join, you will get the result, you want.

    SELECT s.ID, s.Name, l.Name AS Link
FROM Stores s
JOIN Stores l ON s.Link = l.ID;
    Login or Signup to reply.
  3. 0

    You need to use an alias in the sub query otherwise mysql doesn’t know you want to test against stores in the main query eg

    SELECT `ID`, `Name`,
(SELECT `Name` FROM `Stores` xyz  WHERE xyz.ID = Stores.Link ) AS Link 
FROM `Stores` 
WHERE (`ID` LIKE '2');

    a self join (as in other answers) will produce the same result

    nb group is a reserved word in mysql and not a good name for a column.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search