Solved

JOIN TYPE

Posted on 2008-10-03
9
195 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now