Solved

JOIN TYPE

Posted on 2008-10-03
9
196 Views
Last Modified: 2010-03-20
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.
0
Comment
Question by:Kevin Robinson
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22632748
>>if the user has selected this

how do you keep track of what the user selects?
0
 
LVL 3

Author Comment

by:Kevin Robinson
ID: 22632777
The UseAlternateAddress Field
0
 
LVL 4

Expert Comment

by:GO-87
ID: 22632782
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:GO-87
ID: 22632787
P.S. Sorry - my mistake, you are wanting to join on AddressID (which is fine)
0
 
LVL 4

Expert Comment

by:GO-87
ID: 22632791
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
 
LVL 3

Author Comment

by:Kevin Robinson
ID: 22632801
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
 
LVL 4

Expert Comment

by:GO-87
ID: 22632870
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22633053
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22643588
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question