Hello:

For the query experts out there....

I have a many-to-many relationship of contacts and addresses. Some contacts might have
more than one address, some might only have a single address, and some might not have any
address.  Also, an address can be linked to more than 1 contact.

I have a table for the contacts, one for the addresses, and a junction table that links
them together.  Also, the junction table contains a column called "Priority".  If the
priority is "1" then that is the default address for that particular contact.

Here's my problem:

I need to show a list of all contacts, whether they have an address or not.... and if they
have an address, I only want to show the default address (Priority = 1).

So for instance, with the following data:

  Dale - Office address is 1234 Main St. (DEFAULT)
       - Home address 2442 Heron Cir.

  Kim  - Home address 2442 Heron Cir.  (DEFAULT)

  Keaton - No address


I need to geneate the following list:

Dale    1234 Main St.
Kim     2442 Heron Cir.
Keaton
  

I've tried using a Left Outer Join, but I get double entries for Dale, like this:

Dale    1234 Main St.
Dale    2442 Heron Cir.
Kim     2442 Heron Cir.
Keaton


If I put in something like "where Priority = 1" then I no longer get Keaton in the list
(who has no address).




Here is a (simplified) version of the tables I've created.




Contacts Table  |Contact  |
               ----------|
               |Dale     |
               |Kim      |
               |Keaton   |
               -----------


Addresses Table |AddressKey  |Address          |Type      |
               -------------------------------------------
               |Address A   | 1234 Main st.   |Office    |
               |Address B   | 2442 Heron Cir. |Home      |
               |------------|-----------------|----------|



Junction Table  |Contact |AddressKey   |Priority |
               ----------------------------------
               |Dale    | Address A   |    1    |
               |Dale    | Address B   |    2    |
               |Kim     | Address B   |    1    |
               |--------|-------------|---------|



Is there a way to do this?  If so, how? (please be detailed, I'm an SQL newbie).

Thanks,

Dale

> I have a table for the contacts, one for the addresses, and a
> junction table that links them together.  Also, the junction table
> contains a column called "Priority".  If the priority is "1" then
> that is the default address for that particular contact.
>
> Here's my problem:
>
> I need to show a list of all contacts, whether they have an address
> or not.... and if they have an address, I only want to show the
> default address (Priority = 1).

In your JOIN clause, include the Priority = 1 requirement:

 SELECT c.contact, a.address
 FROM contacts c
 LEFT JOIN junctions j ON j.contact = c.contact AND j.Priority = 2
 LEFT JOIN addressses a ON a.AddressKey = j.AddressKey


This is just off the top of my head, I haven't simulated this to be
sure.  But here's my rationale:  If you put the "Priority = 1"
requirement in a WHERE clause, it restricts everything, but with it in
the JOIN clause, it only restricts those records that are returned by
the JOIN.  Yes, you need the "LEFT" to keep from returning contacts
without any address.

BTW, this will show contacts that do not have a default address as not
having any address, even if they have other addresses.  I assume you
have other structures in place to prevent that scenario.

Does that help?

--
David Cornelius
CorneliusConcepts.com

<<< In your JOIN clause, include the Priority = 1 requirement:

 SELECT c.contact, a.address
 FROM contacts c
 LEFT JOIN junctions j ON j.contact = c.contact AND j.Priority = 2
 LEFT JOIN addressses a ON a.AddressKey = j.AddressKey


This is just off the top of my head, I haven't simulated this to be
sure.  But here's my rationale:  If you put the "Priority = 1"
requirement in a WHERE clause, it restricts everything, but with it in
the JOIN clause, it only restricts those records that are returned by
the JOIN.  Yes, you need the "LEFT" to keep from returning contacts
without any address.

BTW, this will show contacts that do not have a default address as not
having any address, even if they have other addresses.  I assume you
have other structures in place to prevent that scenario.

Does that help?

>>>>

David you're a genius!  It looks like it is exactly what I needed.  Regarding the
priority, I force it to be > 0 with a Check constraint.  Making sure that it is always at
least a 1 is done in my code.

Thanks,

Dale

> David you're a genius!  It looks like it is exactly what I needed.
> Regarding the priority, I force it to be > 0 with a Check constraint.
> Making sure that it is always at least a 1 is done in my code.
>
> Thanks,
>
> Dale

Glad to help!



--
David Cornelius
CorneliusConcepts.com

Dave:

To make it a little more interesting, my contacts table contains records that represent
both individual people, or a company.  If the record is for a person, then there is a
field that I call "CompanyKey" that allows the record for a person to be linked to a
company record (in the same table).  (While educating myself on SQL, I was surprised that
you could even do this, but apparently you can!)

So in my list I now want to include the company name,  I've added a third join.

SELECT c.contact, t.contact AS companyName, a.address
FROM contacts c
LEFT JOIN junctions j ON j.contact = c.contact AND j.Priority = 1
LEFT JOIN addressses a ON a.AddressKey = j.AddressKey
LEFT JOIN contacts t ON c.companykey = t.contact

This seems to work, but is it the the right way to do it?  Will all these joins have a
serious performance penalty?

The more I learn about SQL, the more impressed I am with what it can do!

Thanks,

Dale

> So in my list I now want to include the company name,  I've added a
> third join.
>
>  SELECT c.contact, t.contact AS companyName, a.address
>  FROM contacts c
>  LEFT JOIN junctions j ON j.contact = c.contact AND j.Priority = 1
>  LEFT JOIN addressses a ON a.AddressKey = j.AddressKey
>  LEFT JOIN contacts t ON c.companykey = t.contact
>
> This seems to work, but is it the the right way to do it?  Will all
> these joins have a serious performance penalty?

"The right way to do it" is highly subjective and often debated!  But
there are definitely better ways for different situations.

I think JOINs are pretty efficient, and since the "person" record seems
to be acting as a "master" it needs to remain as the main record in the
SELECT list, thus JOINing back to the same table for the secondary
"company" record is the right thing to do.

However, if you were doing a complicated report that had to do
thousands or millions of these joins, it would probably make sense to
create a temporary table in order to flatten the data structure as a
preliminary step.  In that case, you would probably still use the joins
to fill the temporary table, but then access the fields with a simpler
SELECT for the bulk of the report.

As an aside, I've found early versions of MySQL to really mess up with
JOINs and had to resort to long WHERE clauses to get what I was after.
In that case, using a JOIN was NOT "the right way to do it!"

--
David Cornelius
CorneliusConcepts.com

Thanks much David.  You have been very helpful in my ongoing SQL education.

Dale

Dale


Just to jump in here about "Will all these joins have a serious performance penalty?" the answer is YES if you don't have the right indices in use and probably not if you do.

One tool you need to become familiar with is the execution plan. In EDBManager when you create a query you have two checkboxes. Request sensitive result set and request execution plan.

The latter will, on completion of the query, produce a report which you can use to identify any missing indices etc. Queries that run well in test situations with little data can bring an app to its knees with a well populated dataset because the necessary indices are missing. You can be talking milliseconds going to minutes.

Roy Lambert [Team Elevate]

<<< One tool you need to become familiar with is the execution plan. In EDBManager when
you create a query you have two checkboxes. Request sensitive result set and request
execution plan. >>>


Hi Roy:

Yes I discovered the value of the execution plan from some of your previous posts.

Thanks,

Dale


PS:

You're a busy guy!  I see your posts scattered across several forums in the Delphi world.

Dale

>You're a busy guy! I see your posts scattered across several forums in the Delphi world.

The reason for that is my money making job is recruitment and I'm not busy

Roy Lambert