Double JOIN state?ment

[Guest_imported]

Hello!

I would like to JOIN 2 tables, but I have encountered problems. This is why...

I have 2 tables:
- TableA contains information about members of an association
- TableB contains information about companies in my region

So far so good... But the problem is that I have 2 fields in TableA that relate to the SAME primary key in Table2 - so TableB's primary key is a foreign key twice in TableA.


My structure (simplified is)

TableA(
IdA,
Name,
Surname,
Employed_Company_ID,
Mailing_Company_ID,
...)

TableB(
IdB,
Company,
Street,
...)

Employed_Company_ID is a filed containing information about where a person is employed and Mailing_Company_ID contains information about the company where the member wants us to send him brochures,... (it seems odd, but to a lot of members we send stuff to a company where they are not employed).

How can I join the tables?


Thanky you,
Rok

 

[karluk]

Sorry, but you haven't given us anything close to enough information to answer your question. Of course you will join the tables by matching one or both of the foreign keys to the primary key of the other table. However, the exact query that you write is totally dependent on what you want to do, and you haven't given us a clue about what you are trying to accomplish.

 

[Guest_imported]

I agree with KARLUK, you can do all sorts of queries with two tables like this, please can you be a bit more specific about what information you want to retrieve ?

However, basically you would join your table B twice to your table A,
once by the employed_comany_id, and once by the mailing_company_id, like

SELECT whatever fields from three files
FROM tableA as a
(LEFT) JOIN tableB as b on a.employed_company_id = b.idB
(LEFT) JOIN tableB as c on a.mailing_company_id = c.idB

But then again, it all depends on what you want to do ....

Regards
milan432

 

[Guest_imported]

Hello!

Thanks for your replies. I would like to retireve all information about a given member: name, surname,... + all the information we have about comapni where he works and company where he wants us to send him mail (address, telephone number, fax number,...)

DO you need any more information?

Bostjan

 

[gregsimpson]

I'm still not sure your requirements are spelt out fully however I think from the information you have given so far the following will satisfy your query

Select IdA,
Name,
Surname,
Employed_Company_ID,
IdB,
Company,
Street,
from tableA, tableB
where IdA = Employed_company_id
union all
Select IdA,
Name,
Surname,
mailing_Company_ID,
IdB,
Company,
Street,
from tableA, tableB
where IdA = mailing_company_id