douzizang7783
douzizang7783
2017-04-10 12:06

SQL Query将两列与另一个表中的一列进行比较(并获取两个值)

已采纳

Is it possible to, based on the ID value of two columns from table1, get their equivalent in table2?

My table1 looks like this:

id | origin | destiny
-- | ------ | -------
1  | 2      | 3
2  | 4      | 5

and table2, like this:

id | name
-- | ----
1  | bla
2  | asd
3  | dfg
4  | qwe
5  | tle

And I want to get something like this:

id | origin | destiny | nameOrigin | nameDestiny
-- | ------ | ------- | ---------- | -----------
1  | 2      | 3       | asd        | dfg
2  | 4      | 5       | qwe        | tle

I tried to do two queries:

SELECT
    t1.origin, 
    t1.destiny,
    t2.name
FROM 
    table1 t1 
INNER JOIN table2 t2 ON t1.origin = t2.id

and:

SELECT
    t1.origin, 
    t1.destiny,
    t2.name as destinyName
FROM 
    table1 t1 
INNER JOIN table2 t2 ON t1.destiny = t2.id

But if I delete a value from one table, the another one keeps indexing that row hence there is an undefined offset problem.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dongshanyan0322 dongshanyan0322 4年前

    You can join the second table twice:

    SELECT      t1.id,
                t1.origin, 
                t1.destiny,
                o.name as nameOrigin,
                d.name as nameDestiny
    FROM        table1 t1 
    INNER JOIN  table2 o ON t1.origin = o.id
    INNER JOIN  table2 d ON t1.destiny = d.id
    

    NB: "destiny" is not the same thing as "destination".

    点赞 评论 复制链接分享
  • dtntjwkl83750 dtntjwkl83750 4年前

    You can do it in a simple query:

    SELECT t1.id,t1.origin,t1.destiny,o.name as nameOrigin, d.name as nameDestiny  
    FROM t1,t2 o, t2 d 
    WHERE t1.origin=o.id AND t1.destiny=d.id
    

    I am attaching SQL fiddle link here. http://sqlfiddle.com/#!9/ecf5ae

    点赞 评论 复制链接分享