从表1到表2连接2列

How would you reference table1 columns to 2 columns in table 2

如何将table1列引用到表2中的2列

I created a table 'State' with 50 exact rows

我创建了一个包含50个确切行的表'State'

trying to relate (weddingState,contactState) in 'Wedding' table

试图在“婚礼”表格中联系(weddingState,contactState)

This is the statement that I created but it only joins the top WeddingState correctly - seems not to care about the INNER Join below it...

这是我创建的语句,但它只能正确加入*WeddingState - 似乎不关心它下面的INNER Join ...

SELECT *

FROM weddings

INNER JOIN states as s1 ON weddings.WeddingState = s1.StateId //state of marriage

INNER JOIN在婚礼上表示为s1。婚礼状态= s1.StateId //结婚状态

INNER JOIN states as s2 ON weddings.ContactState = s2.StateId //contact state of bride

INNER JOIN声明为s2 ON weddings.ContactState = s2.StateId //接触新娘的状态

WHERE weddings.weddingid="094829292"

4 个解决方案

#1

I'd guess that you're retrieving these in PHP or something, and you're fetching the rows in a hash-array, keyed by the field name. Of course there can only be one element in a hash with a given key. So you need to use column aliases to make sure columns with the same name are given a distinct alias.

我猜你用PHP或其他东西检索它们,并且你在哈希数组中获取行,由字段名称键入。当然,在给定键的哈希中只能有一个元素。因此,您需要使用列别名来确保具有相同名称的列具有不同的别名。

SELECT w.*, s1.StateID AS wstate, s2.StateId AS cstate
FROM weddings AS w
INNER JOIN states AS s1 ON w.WeddingState = s1.StateId //state of marriage
INNER JOIN states AS s2 ON w.ContactState = s2.StateId //contact state of bride
WHERE w.weddingid="094829292";

Now your hash-array will have keys "wstate" and "cstate". Without aliasing these columns, one will always overwrite the other.

现在你的哈希数组将有“wstate”和“cstate”键。如果没有别名这些列,将始终覆盖另一列。

#2

And what are you getting for your result that leads you to your conclusion?

你得到的结果是什么让你得出结论?

It's going to be confusing for starters because the field names in the two joins, plus some of the field names in the primary table, are identical. It's a really good idea to explicitly choose your output columns and give them meaningful aliases.

这对初学者来说会让人感到困惑,因为两个联接中的字段名称加上主表中的一些字段名称是相同的。明确选择输出列并为它们提供有意义的别名是一个非常好的主意。

#3

How about:

SELECT s1.StateName, s2.StateName

SELECT s1.StateName,s2.StateName

FROM weddings

INNER JOIN states as s1 ON weddings.WeddingState = s1.StateId //state of marriage

INNER JOIN在婚礼上表示为s1。婚礼状态= s1.StateId //结婚状态

INNER JOIN states as s2 ON weddings.ContactState = s2.StateId //contact state of bride

INNER JOIN声明为s2 ON weddings.ContactState = s2.StateId //接触新娘的状态

WHERE weddings.weddingid="094829292"

#4

Thanks Bill, I added the StateName as well

谢谢Bill,我也添加了StateName

SELECT w.*,

s1.StateId AS WeddingStateId,

s1.StateName AS WeddingStateName,

s2.StateId AS ContactStateId,

s2.StateName AS ContactStateName

FROM weddings AS w

来自婚礼AS w

INNER JOIN states AS s1 ON w.WeddingState = s1.StateId

INNER JOIN表示AS s1 ON w.WeddingState = s1.StateId

INNER JOIN states AS s2 ON w.ContactState = s2.StateId

INNER JOIN表示AS s2 ON w.ContactState = s2.StateId