Hello:

Need some help with a query involving a junction table and joins.  I don't yet have a
solid understanding of Joins and want to make sure that I am not overlooking anything here.

I have a contacts table:
ctContactKey
ctContactnName

An Address Table
adAddressKey
adAddress1
adAddressCity
adAddressState
adAddressZip

And a junction table joining the two tables.  There is a many-to-many relationship of
contacts and addresses through the use of the junction table.
jContactKey
jAddressKey


Now I need to let my users create a list of contacts by seaching for an address.  Right
now, I have something like this.

SELECT ct.ctContactName, ad.adAddress1, ad.adAddressCity, ad.adAddressState, ad.adAddressZip
FROM Addresses ad
LEFT OUTER JOIN JunctionTable j on j.jAddressKey = adAddressKey
LEFT OUTER JOIN Contacts ct ON ct.ctContactKey = j.jContactKey
WHERE ad.adAddressCity = 'Sarasota';


Am I on the right track here?  This seems to work but I want to make sure that I am using
the joins correctly and am not overlooking something.  In particular I want to make sure I
don get inner.outer and left/right joins mixed up.

Thanks,

Dale

Looks fine to me.

The ct.ctContactName value of can be NULL here if there is a an address not
assigned to any contact.

If your data is "clean"  (meaning junction table does not contain any
"orphaned" records or records with contact key being null) then 2nd join
could in theory be  regular (inner) join and does not have to be left outer.
However keeping it left outer might make it easier to read.

I assume you do need the many-to-many capability that junction provides as
it does make sql a bit more complex than one-to-many.

Raul


"Dale Derix" <dale@emerald-data.com> wrote in message
news:9211DF4F-388D-4B87-911A-29E59D7A3A45@news.elevatesoft.com...
> Hello:
>
> Need some help with a query involving a junction table and joins.  I don't
> yet have a
> solid understanding of Joins and want to make sure that I am not
> overlooking anything here.
>
> I have a contacts table:
> ctContactKey
> ctContactnName
>
> An Address Table
> adAddressKey
> adAddress1
> adAddressCity
> adAddressState
> adAddressZip
>
> And a junction table joining the two tables.  There is a many-to-many
> relationship of
> contacts and addresses through the use of the junction table.
> jContactKey
> jAddressKey
>
>
> Now I need to let my users create a list of contacts by seaching for an
> address.  Right
> now, I have something like this.
>
> SELECT ct.ctContactName, ad.adAddress1, ad.adAddressCity,
> ad.adAddressState, ad.adAddressZip
> FROM Addresses ad
> LEFT OUTER JOIN JunctionTable j on j.jAddressKey = adAddressKey
> LEFT OUTER JOIN Contacts ct ON ct.ctContactKey = j.jContactKey
> WHERE ad.adAddressCity = 'Sarasota';
>
>
> Am I on the right track here?  This seems to work but I want to make sure
> that I am using
> the joins correctly and am not overlooking something.  In particular I
> want to make sure I
> don get inner.outer and left/right joins mixed up.
>
> Thanks,
>
> Dale
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 4845 (20100207) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4846 (20100208) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Hi Raul:

Thanks for looking this over.   You mentioned an inner join...  Is there any performance
benefit?

Dale

Dale,

<< Thanks for looking this over.   You mentioned an inner join...  Is there
any performance benefit? >>

No.  There's a little tiny it of extra processing for left outer joins, but
I doubt if you'll notice any difference.

--
Tim Young
Elevate Software
www.elevatesoft.com

This is a a question Tim can answer as I would be only guessing.

However I like guessing and I doubt it would a make a lot of difference in
this case though. The outer and inner join seem to do almost same amount on
work once you hit the contact table (since the address and junction have
already been processed) - we'll see if Tim proves me wrong though.

Main thing in regards to performance is to ensure that you have indexes for
the key fields so that edb can use them as much as possible.

I strongly suggest running the SQL in EDBManager and use the "request
execution plan" option - that will tell you if something is not optimized
and/or if indexes are not used (you can do it in code as well but I find it
easier to just run it in edb manager). Ideally use a non-trivial amount of
data in all the tables.

Raul


"Dale Derix" <dale@emerald-data.com> wrote in message
news:EC0C19EF-2ACA-4B12-AD11-E551A920BB98@news.elevatesoft.com...
> Hi Raul:
>
> Thanks for looking this over.   You mentioned an inner join...  Is there
> any performance
> benefit?
>
> Dale



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4848 (20100208) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Thanks Raul:

Yes, I've been prototyping my queries in the ElevateDB Manager, using anywhere from 40k to
200k records.  It has been immensely helpful.

Dale