Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Working with junction tables |
Sun, Feb 7 2010 2:39 PM | Permanent Link |
Dale Derix | 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 |
Sun, Feb 7 2010 10:43 PM | Permanent Link |
"Raul" | 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 |
Mon, Feb 8 2010 10:04 AM | Permanent Link |
Dale Derix | Hi Raul:
Thanks for looking this over. You mentioned an inner join... Is there any performance benefit? Dale |
Mon, Feb 8 2010 10:10 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Feb 8 2010 10:24 AM | Permanent Link |
"Raul" | 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 |
Mon, Feb 8 2010 11:02 AM | Permanent Link |
Dale Derix | 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 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |