Solved

JOIN TYPE

Posted on 2008-10-03
9
193 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 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

18 Experts available now in Live!

Get 1:1 Help Now