Thread: SQL Joins
Can someone please tell me what this previously-written SQL is doing? I don't understand how it is structured. I'm familiar with JOINs such as this (1 joined table per 1 ON phrase): FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id LEFT OUTER JOIN t3 ON t2.name = t3.name But the following SQL is puzzling me. To which JOINs are the ON phrases connected? Any help would be appreciated! (Feel free to re-format the physical structure of the lines....as this is the way it came to me!) Trace' FROM eStoreManager.estore_dept_prod LEFT OUTER JOIN eStoreManager.estore_product ON eStoreManager.estore_dept_prod.sku= eStoreManager.estore_product.sku RIGHT OUTER JOIN eStoreManager.tblCProductClass LEFT OUTER JOIN eStoreManager.tblCProductClassProductON eStoreManager.tblCProductClass.ProductClassID = eStoreManager.tblCProductClassProduct.ProductClassID ON eStoreManager.estore_product.sku = eStoreManager.tblCProductClassProduct.sku FULL OUTER JOIN eStoreManager.tblCProductClassCreator INNER JOIN eStoreManager.tblCCreator ON eStoreManager.tblCProductClassCreator.CreatorID = eStoreManager.tblCCreator.CreatorID ON eStoreManager.tblCProductClass.ProductClassID = eStoreManager.tblCProductClassCreator.ProductClassID
Trace, is the second SQL in Postgres? "Trace" <tjennette@thomasnelson.com> wrote in message news:56893385.0201081316.25272d8c@posting.google.com... > Can someone please tell me what this previously-written SQL is doing? > I don't understand how it is structured. I'm familiar with JOINs such > as this (1 joined table per 1 ON phrase): > > FROM t1 > LEFT OUTER JOIN t2 ON t1.id = t2.id > LEFT OUTER JOIN t3 ON t2.name = t3.name > > But the following SQL is puzzling me. To which JOINs are the ON > phrases connected? > > Any help would be appreciated! (Feel free to re-format the physical > structure of the lines....as this is the way it came to me!) > > Trace' > > > FROM eStoreManager.estore_dept_prod LEFT OUTER JOIN > eStoreManager.estore_product ON > eStoreManager.estore_dept_prod.sku = > eStoreManager.estore_product.sku > RIGHT OUTER JOIN > eStoreManager.tblCProductClass > LEFT OUTER JOIN eStoreManager.tblCProductClassProduct ON > eStoreManager.tblCProductClass.ProductClassID = > eStoreManager.tblCProductClassProduct.ProductClassID > ON > eStoreManager.estore_product.sku = > eStoreManager.tblCProductClassProduct.sku > FULL OUTER JOIN > eStoreManager.tblCProductClassCreator INNER JOIN > eStoreManager.tblCCreator > ON eStoreManager.tblCProductClassCreator.CreatorID = > eStoreManager.tblCCreator.CreatorID > ON eStoreManager.tblCProductClass.ProductClassID = > eStoreManager.tblCProductClassCreator.ProductClassID
tjennette@thomasnelson.com (Trace) writes: > Can someone please tell me what this previously-written SQL is doing? > I don't understand how it is structured. Apparently whoever wrote this doesn't believe in parentheses. I'd think it a lot more readable with parentheses and appropriate indentation, viz: FROM ( (eStoreManager.estore_dept_prod LEFT OUTER JOIN eStoreManager.estore_product ON eStoreManager.estore_dept_prod.sku= eStoreManager.estore_product.sku) RIGHT OUTER JOIN (eStoreManager.tblCProductClassLEFT OUTER JOIN eStoreManager.tblCProductClassProduct ON eStoreManager.tblCProductClass.ProductClassID= eStoreManager.tblCProductClassProduct.ProductClassID) ON eStoreManager.estore_product.sku= eStoreManager.tblCProductClassProduct.sku) FULL OUTER JOIN (eStoreManager.tblCProductClassCreator INNER JOIN eStoreManager.tblCCreator ON eStoreManager.tblCProductClassCreator.CreatorID= eStoreManager.tblCCreator.CreatorID) ON eStoreManager.tblCProductClass.ProductClassID = eStoreManager.tblCProductClassCreator.ProductClassID BTW, since Postgres doesn't have schemas you'd need to lose the "eStoreManager." prefixes, which aren't doing anything for readability here either: FROM ( (estore_dept_prod LEFT OUTER JOIN estore_product ON estore_dept_prod.sku = estore_product.sku) RIGHT OUTER JOIN (tblCProductClass LEFT OUTER JOIN tblCProductClassProduct ON tblCProductClass.ProductClassID = tblCProductClassProduct.ProductClassID) ON estore_product.sku = tblCProductClassProduct.sku) FULL OUTER JOIN (tblCProductClassCreator INNER JOIN tblCCreator ON tblCProductClassCreator.CreatorID = tblCCreator.CreatorID) ON tblCProductClass.ProductClassID = tblCProductClassCreator.ProductClassID Does that help any? If you're wondering how I knew where to put the parentheses, it's because there's no place else they could go and still have a syntactically valid statement. So I suppose the author felt they were unnecessary. But I think it's more readable with 'em. regards, tom lane