joining 2 columns from Table1 to Table 2

Question

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

I created a table 'State' with 50 exact rows

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

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...

SELECT *

FROM weddings

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

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

WHERE weddings.weddingid="094829292"

Answer 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.

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.

Answer 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.

Answer 3

How about:

SELECT s1.StateName, s2.StateName

FROM weddings

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

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

WHERE weddings.weddingid="094829292"

Answer 4

Thanks Bill, I added the StateName as well

SELECT w.*,

s1.StateId AS WeddingStateId,

s1.StateName AS WeddingStateName,

s2.StateId AS ContactStateId,

s2.StateName AS ContactStateName

FROM weddings AS w

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

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