radox

I don't even know if this is possible, but here it goes.

I have two tables.

the first one (contacts) contains three columns (id,firstname, lastname)
the second (members) contains two columns (id1, id2) both are foreign keys to (contacts)

how do i produce one join to give me both firstname and lastname columns?

the below select doesn't work, just trying to show my thinking:


I'm I making any sense? Is this even possible?
There are obviously more columns to my tables. Getting this info would get my answer though.

Thanks in advance.

Azkaban

It might help if you provide an explanation of what you're really trying to do, in other words WHY you want firstname and lastname twice per line. There could be another way to do this.

I always found self joins confusing, so I may not be able to help you. But I'll try if you can explain better too.

A couple tips though. When I've seen self joins there's always a renaming of the table like

SELECT t1.x,t2.x FROM mytable AS t1, mytable as t2

In the join

FROM member as m1
FROM member as m2

etc......

I also doubt you are putting the join together correctly. You'll need to reference the member in the FROM rather than the join...again, I believe.

radox

thanks for you reply.

id1 and id2 are both foreign keys to the (contacts) table.

the (firstname) for id1 will be different than the (firstname) of id2

i can easily join id1 by:


that will give me firstname and lastname for id1

i need to pull from the same table again and get firstname and lastname for id2

i'm trying to get this done in one SELECT statement....

maybe it can't be done??

Azkaban

I think it can be done even though I don't have the answer on my tongue. But I'm still trying to understand why you need it. You don't need to give me the real example if this is a private project. But any example doing the same type of thing would probably help me come up with a solution for you. Helps me jog the thinking when I can apply the situation to the real world.

radox

gotcha...

ok, here's the real example.

this is for a mortgage company...i just tried to generalize the example.

there is a table for each borrower file.
there is a table for all our investors.

there can be one investor on the 1st mortgage and a different one for the 2nd mortgage.

the borrower table has a integer column (firstmort) and integer columun (secmort)

i need to join these int columns to get the names of the investors.

did i confuse this more or make some sense?

Azkaban

That helps a lot. First of all, I find it easier to visualize this by being more descriptive when naming the id's. Instead of just "id" in the contacts table, how about contactid?

Second, you are missing a primary key in the second table. Rather than id1 and id2, make it memberid,investorid1,investorid2.

Then try this (untested, my syntax may be slightly off, but this concept should do the trick):

SELECT t1.firstname as f1,t1.lastname as l1,t2.firstname as f2,t2.lastname as l2
FROM contacts as t1,contacts as t2
LEFT JOIN members ON t1.contactid=members.investorid1
LEFT JOIN members ON t2.contactid=members.investorid2

radox

Concept looks good. I tried every possible variation. Looks like joining to the same table twice is not allowed. i keep getting "not unique table/alias"

radox

I got it. Need to alias the tables as well.

SELECT t1.firstname as f1,t1.lastname as l1,t2.firstname as f2,t2.lastname as l2
FROM contacts as t1,contacts as t2
LEFT JOIN members AS m1 ON t1.contactid=m1.investorid1
LEFT JOIN members AS m2 ON t2.contactid=m2.investorid2

Tricky Sh!t...

Thanks so much for you help.

radox

This only works for one record...
When there is more than one record, the results go up by that much.

In other words:

1 rows x 1 = 1 results
2 rows x 2 = 4 results
3 rows x 3 = 9 results

I don't know how to use GROUP BY correctly


This is so frustrating