CODEWITHSUNDEEP
mysqljoin
MikeS
MikeS

Reputation: 383

Selecting a column from a table in MySQL twice

I have table a which stores the user id and the ids of his origin and destination. On table b I have the location id and the specific name of the place. What I'm trying to do is join the tables but the name column from table b will have to be used twice since I'm trying to get 2 addresses. I'm trying to read up on MySQL but just keep doing it wrong. Any help would be appreciated.

      table a
------------------------
|  uid |   to | from   |
------------------------
|   1  |   1  |   2    |
------------------------

    table b
---------------
| lid  | name | 
---------------
|   1  | one  |
---------------
|   2  | two  |
---------------

       /what I'm trying to achieve/
------------------------------------------
|a.uid | a.to | b.name | a.from | b.name |
------------------------------------------
|   1  |   1  |  one   |   2    | two    |
------------------------------------------

Upvotes: 1

Views: 425

Answers (2)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39951

If it's limited to just being twice just join in table b twice.

But it looks like you could have any number of numbers between a.from and a.to and in that case I would suggest that you do this in two or more queries.

One to get the row from a and than one to get all rows in b that is between a.from and a.to.

Upvotes: 0

Tomas
Tomas

Reputation: 59435

You will have to join table b twice, and every time using different table name (b1, b2) using as

select *
from a join b as b1 on a.to = b1.lid 
       join b as b2 on a.from = b2.lid

so the result would be

--------------------------------------------
|a.uid | a.to | b1.name | a.from | b2.name |
--------------------------------------------
|   1  |   1  |  one    |   2    | two     |
--------------------------------------------

but what you probably want is to prevent name clash - if you e.g. call it from PHP - so then also rename the columns:

select a.*, b1.name as toName, b2.name as fromName
... (rest of the query as above)

Upvotes: 1

Related Questions