Solved

JOIN TYPE

Posted on 2008-10-03
9
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need to create and populate a column map table 5 64
Help with SQL pivot 11 50
Migrate SQL 2005 DB to SQL 2016 4 33
Importing from CSV to SSMS 2 18
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

710 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