当前位置: 动力学知识库 > 问答 > 编程问答 >

MYSQL Select colum A or B if either is = X and Join on the other non-X-value in Colum A or B

问题描述:

I have a table of pairwise distances of items.

Given an Item ID I want to list the Paired ItemID's.

I then want to join this to another table that has more details on the other Items that match.

Table A.

 +-------+-----------+----------+

| ItemA | ItemB | Distance |

+-------+-----------+----------+

| 12 | 33 | 13.0 |

| 12 | 4 | 14.6 |

| 43 | 12 | 17.8 |

| 44 | 12 | 19.6 |

+-------+-----------+----------+

My best guess is to select a row that has X as a Value in either the ItemA or ItemB, and have an output column AS ITEM.

Semi-complete/wrong Mysql:

Select *, (NON X Column Value) AS ITEM FROM TableA WHERE (ItemA = X) OR (ItemB = X)

I can add the JOIN part to the query later.

Thanks

网友答案:

(NON X Column Value) should be like CASE WHEN ItemA = X THEN ItemB ELSE ItemA END

网友答案:
SELECT ItemA as Start, ItemB as Destination
FROM   TableA
WHERE  ItemA = X
UNION
SELECT ItemB as Start, ItemA as Destination
WHERE  ItemB = X

Once you have the table above you can then join it with the other table to show more details.

Hope this helps

分享给朋友:
您可能感兴趣的文章:
随机阅读: