JOIN TYPE

I am working on changes to my contact database.  In the database there is a table for the Organisations and one for the Contacts.  

What I need to add is an option to have an alternate address for an alternate address.  So I added two field to the contact table.
AlternateAddressID (Which Links to the new address if there is one, default is 0)
UseAlternateAddress (Boolean which the user specifies to use the alternate address or not)

I though this was fine but then I though when I am doing my normal joins from the Contact to Organisation tables i want the alternate address to join (AlternateAddressID) if the user has selected this other wise join to the orginal address (OrgID).  Is there a way to join like this or is there a better way to do this.
LVL 3
Kevin RobinsonPrivate VB.NET ContractorAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
try this:

SELECT ...
FROM Organisations org
INNER JOIN Contacts con ON org.AddressID = case when UseAlternateAddress =  'FALSE' THEN con.AddressID ELSE con.AlternateAddressID END
0
 
chapmandewCommented:
>>if the user has selected this

how do you keep track of what the user selects?
0
 
Kevin RobinsonPrivate VB.NET ContractorAuthor Commented:
The UseAlternateAddress Field
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
GO-87Commented:
If I am understanding you correctly, you want to join on address. This sounds dangerous, since the addresses may have been mistyped in one of the tables (it's better to join on a key field, e.g. of type integer or a code)
Anyway, assuming you DO want to join on address, one way would be to do two queries, one joining on Address where UseAleternateAddress = False
and the other query joining on Alternate Address (where UseAleternateAddress = True)
and then combine the output with the UNION keyword:

SELECT ... 
FROM Organisations org
INNER JOIN Contacts con ON org.Address = con.Address
WHERE con.UseAlternateAddress = False
UNION
SELECT ... 
FROM Organisations org
INNER JOIN Contacts con ON org.Address = con.AlternateAddress
WHERE con.UseAlternateAddress = True

Open in new window

0
 
GO-87Commented:
P.S. Sorry - my mistake, you are wanting to join on AddressID (which is fine)
0
 
GO-87Commented:
So my suggestion becomes
SELECT ... 
FROM Organisations org
INNER JOIN Contacts con ON org.AddressID = con.AddressID
WHERE con.UseAlternateAddress = False
UNION
SELECT ... 
FROM Organisations org
INNER JOIN Contacts con ON org.AddressID = con.AlternateAddressID
WHERE con.UseAlternateAddress = True
 

Open in new window

0
 
Kevin RobinsonPrivate VB.NET ContractorAuthor Commented:
Im normally joining on Contact.OrgID  to Organisation.OrgID.  What I would like to join is Contact.OrgID  to Organisation.OrgID
If Contact.UsAlternateAddress is True or Contact.AlteranteAddressID  to Organisation.OrgID if it is selected.
0
 
GO-87Commented:
OK, then try this:
SELECT ... 
FROM Organisations org
INNER JOIN Contacts con ON org.OrgID = con.OrgID
WHERE con.UseAlternateAddress = False
UNION
SELECT ... 
FROM Organisations org
INNER JOIN Contacts con ON org.OrgID = con.AlternateAddressID
WHERE con.UseAlternateAddress = True
  

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Little bit confusing as to which table owns the address details...

select c.name, o.address, a.address, case when c.usealternateaddress = 1 then a.address else o.address end as preferred_address
from contacts c
left outer join organisations o ON o.OrgID = c.OrgID    -- where address lives
left outer join organisations a ON a.OrgID = c.AlternateAddressID    -- where alternate address lives

or, if jut the alt address, then

select c.name, o.address
from contacts c
left outer join organisations o ON o.OrgID = case when c.usealternateaddress = 1 then c.AlternateAddressID else c.orgid end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.