19 Replies Latest reply on Feb 16, 2010 12:23 PM by -==cfSearching==-

    Making a join seems simple but I can't get it to work

    whereskris

      Hi All,

       

      I have 2 tables LUGallery and LUSubGallery, the tables are related by the GalleyID field

       

      LUGallery

       

      Gallery ID
      ClientIDGalleryName
      550Australia
      850Weddings
      1233Portraits
      433Landscapes

       

       

       

      LUSubGallery

       

      SubGalleryIDGalleryIDGalleryName
      675NSW
      685QLD
      698Reception
      708Location
      878Ceromony
      974Rain Forest

       

      What I am try to do seems simple but I can't get it to work the way I want it.

      I am trying to write a query to display Galleries from the LUGallery tbl that have a Sub Gallery attached to them (ie. It's GalleryID appears in the LUSubGallery tbl)

       

      So the query for the above example would list Australia, Weddings & Landscapes

       

       

       

      I have tried to write code both with inner joins and nested queries but just can't get it right,

       

      Any help would be greatly appreciated.

       

      Thanks in advance

      Kris

        • 1. Re: Making a join seems simple but I can't get it to work
          -==cfSearching==- Level 4

          I have tried to write code both with inner joins and nested

          queries but just can't get it right,

           

          Can you post your query?

          • 2. Re: Making a join seems simple but I can't get it to work
            whereskris Level 1

            I have tried this join

             

             

            <cfquery name="join" datasource="#Application.dsn#">
            SELECT     LUGallery.GalleryID, LUGallery.ClientID, LUGallery.GalleryName, LUGallery.Rating, LUGallery.GalleryImage, LUGallery.GalleryInformation,
                                  LUGallery.DisplayOnWebsite, LUGallery.DisplayType, LUGallery.Username, LUGallery.Password, LUGallery.GalleryDateAdded,
                                  LUGallery.GalleryDateEdited, LUGallery.DirName, LUGallery.Locked, LUSubGallery.SubGalleryID, LUSubGallery.GalleryID AS Expr1,
                                  LUSubGallery.GalleryName AS Expr2, LUSubGallery.Rating AS Expr3, LUSubGallery.GalleryImage AS Expr4,
                                  LUSubGallery.GalleryInformation AS Expr5, LUSubGallery.DisplayOnWebsite AS Expr6, LUSubGallery.DisplayType AS Expr7,
                                  LUSubGallery.GalleryDateAdded AS Expr8, LUSubGallery.GalleryDateEdited AS Expr9
            FROM         LUGallery INNER JOIN
                                  LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID
            </cfquery>

             

            It returns all of the records from both tables that Have the same GalleryID, this is no good to me as I just want to display the GalleryNames from the LUGallery tbl once

             

             

             

            Kris

            • 3. Re: Making a join seems simple but I can't get it to work
              -==cfSearching==- Level 4

              I just want to

              display the GalleryNames from the LUGallery tbl once

               

              Then you probably do not want include all the extra columns from the LUSubGallery table.

               

              It returns all of the records from both tables that Have

              the same GalleryID

               

              That is the way this type of JOIN works. It will return one record for each matched GalleryID. If you only want to display the unique gallery names you can either:

               

              1) Use an EXISTS clause.  It will return the distinct records from the main gallery table IF a matching record exists in the LUSubGallery table

               

              --- Not tested ---

              SELECT  LUGallery.GalleryID,

              LUGallery.GalleryName

              FROM    LUGallery

              WHERE   EXISTS (

                      SELECT  *

                      FROM LUSubGallery

                      WHERE LUGallery.GalleryID = LUSubGallery.GalleryID

                      )

               

               

              http://www.techonthenet.com/sql/exists.php

               

              ... OR ....

               

              2) Use the DISTINCT operator to return only the unique combinations of the selected columns. Note: DISTINCT considers all columns in the SELECT list . So if you only want unique galleries, do not include the LUSubGallery columns in the SELECT list.

               

              http://www.w3schools.com/SQl/sql_distinct.asp

               

              Message was edited by: -==cfSearching==-

              1 person found this helpful
              • 4. Re: Making a join seems simple but I can't get it to work
                Dan Bracuk Level 5

                You can use the group attribute of cfoutput query="yourquery" to get the gallery names to display just once.  You have a problem with your database design though.  Storing the clientid in the gallery table is forcing you to have duplicate records.

                • 5. Re: Making a join seems simple but I can't get it to work
                  -==cfSearching==- Level 4

                  Storing the clientid in the gallery table is

                  forcing you to have duplicate records.

                   

                  Not necessarily. How are you defining unique gallery names? Unique galleries by client, or just unique names period.

                   

                  You can use the group attribute of cfoutput

                  query="yourquery" to get the gallery names to display just

                  once. 

                   

                  Generally it is better to retrieve only the data needed, rather than grabbing everything, but only outputting a subset of the records. Granted if the resultset is small, performance differences may not be significant.

                  • 6. Re: Making a join seems simple but I can't get it to work
                    whereskris Level 1

                    Yes you are right I have not got duplicate records As I am displaying galleries by ClientID, Sub galleries are then displayed if the gallery has sub galleries.

                     


                    This code you posted looks like what I am after

                     

                    SELECT  LUGallery.GalleryID,

                    LUGallery.GalleryName

                    FROM    LUGallery

                    WHERE   EXISTS (

                            SELECT  *

                            FROM LUSubGallery

                            WHERE LUGallery.GalleryID = LUSubGallery.GalleryID

                            )

                     

                    but I get the following errror.

                     

                    Error Executing Database Query.

                     

                    [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'LUSubGallery'.

                     

                    Thanks for trying to help me.

                    • 7. Re: Making a join seems simple but I can't get it to work
                      -==cfSearching==- Level 4

                      but I get the following errror.

                       

                         It looks okay. Though I did not test it. Try running the statement

                         directly in your database. It usually provides better error messages.

                      • 8. Re: Making a join seems simple but I can't get it to work
                        Adam Cameron. Level 5

                        but I get the following errror.

                         

                           It looks okay. Though I did not test it. Try running the statement

                           directly in your database. It usually provides better error messages.

                         

                        It runs fine for me.

                         

                        So I don't think that is the entirety of the <cfquery> being run.

                         

                        Can you post the actual code.

                         

                        --

                        Adam

                        • 9. Re: Making a join seems simple but I can't get it to work
                          whereskris Level 1

                          HI This is the query that is causing the error in coldfusion

                           

                           

                          <cfquery name="exists" datasource="#Application.dsn#">
                          SELECT  LUGallery.GalleryID, LUGallery.GalleryName
                          FROM    LUGallery
                          WHERE   EXISTS (
                                  SELECT  *
                                  FROM LUSubGallery
                                  WHERE LUGallery.GalleryID = LUSubGallery.GalleryID
                                  )
                                  LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID
                          </cfquery>

                           

                           

                           

                          Also I get this error

                           

                          Msg 102, Level 15, State 1, Line 8
                          Incorrect syntax near 'LUSubGallery'.

                           

                          when I run The query

                           

                          SELECT  LUGallery.GalleryID, LUGallery.GalleryName
                          FROM    LUGallery
                          WHERE   EXISTS (
                                  SELECT  *
                                  FROM LUSubGallery
                                  WHERE LUGallery.GalleryID = LUSubGallery.GalleryID
                                  )
                                  LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID

                           

                          In SQL Server Management Studio Express

                          • 10. Re: Making a join seems simple but I can't get it to work
                            whereskris Level 1

                            Thanks to everyone for their input I have got a soloution, I'm not sure if it is the best or not but it works and it is a combination of a join and group attribute for cfquery.

                             

                            <cfquery name="rsGetGalleriesWithSubGalleries" datasource="#Application.dsn#">
                            SELECT LUGallery.GalleryID, LUGallery.GalleryName
                            FROM LUGallery
                            INNER JOIN LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID
                            WHERE ClientID = '#Application.ClientID#'
                            </cfquery>

                             

                            <cfoutput query="rsGetGalleriesWithSubGalleries" group="GalleryName">
                            #rsGetGalleriesWithSubGalleries.GalleryName#<br>
                            </cfoutput>

                             

                             

                            Thanks for your input.

                             

                            Kris

                            • 11. Re: Making a join seems simple but I can't get it to work
                              -==cfSearching==- Level 4

                              HI This is the query that is causing the error in

                              coldfusion

                               

                              Try running the query you posted earlier.  Notice, it does not have the extra "LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID" statement at the end. That is what is causing the error. Remove it and it should work properly.

                               

                               

                              SELECT  LUGallery.GalleryID, LUGallery.GalleryName

                              FROM    LUGallery

                              WHERE   EXISTS (

                                      SELECT  *

                                      FROM LUSubGallery

                                      WHERE LUGallery.GalleryID = LUSubGallery.GalleryID

                                      )

                               

                               

                               

                              -Leigh

                              • 12. Re: Making a join seems simple but I can't get it to work
                                Dan Bracuk Level 5

                                That query will only return galleries that have sub-galleries.  This may or may not be what you wanted.

                                • 13. Re: Making a join seems simple but I can't get it to work
                                  whereskris Level 1

                                  Yes this is exactly what I was after

                                   

                                   

                                  I am trying to write a query to display Galleries from the LUGallery tbl that have a Sub Gallery attached to them (ie. It's GalleryID appears in the LUSubGallery tbl)

                                   

                                   

                                  Maybe I could have said it a bit simpler.

                                  • 14. Re: Making a join seems simple but I can't get it to work
                                    Chiwi8888

                                    Why even use the EXISTS clause at all? I am pretty sure this only runs a join type query underneath the covers anyway.

                                     

                                    Much simplier would be:

                                     

                                    select * from LUGallery

                                    where GalleryID in (select GalleryID from LUSubGallery)

                                     

                                    You should pretty much only use JOINS if you want columns from both tables, other wise like above just use IN.  Also dont use select * like I have in production code.

                                     

                                    Cheers

                                    • 15. Re: Making a join seems simple but I can't get it to work
                                      -==cfSearching==- Level 4

                                      I believe what you are suggesting is the opposite of what the OP is after ..

                                      • 16. Re: Making a join seems simple but I can't get it to work
                                        Chiwi8888 Level 1

                                        I dont see how its the opposite, considering it fits his simplified description of what he is trying to do.  It is also doing the same thing as your last suggested sql statement, just simplier and it should also be faster.

                                         

                                        Maybe he will get back to us and let is know.

                                         

                                        Cheers

                                        • 17. Re: Making a join seems simple but I can't get it to work
                                          -==cfSearching==- Level 4
                                          I dont see how its the opposite, considering it fits his simplified description of what he is trying to do. 

                                           

                                          My bad. That is what happens when you skim ...

                                           

                                          ... just simplier

                                          Personally, I do not find it is simpler. Just a slightly different way of writing it.  But yes, both options are perfectly valid.

                                           

                                          ... and it should also be faster.

                                          Why do you say that?

                                          • 18. Re: Making a join seems simple but I can't get it to work
                                            Chiwi8888 Level 1

                                            ... and it should also be faster.

                                            Why do you say that?

                                             

                                            Experience has shown me that it is faster in cases I have had in the past on various db's.  There are also quite a few blog entries around that discuss the subject; for and against i must admit.

                                             

                                            However, I am using MS SQL 2005 at the moment and tried out a demo of the two different query types.  What is interesting, is that the execution plan is exactly the same.  So the query optimiser seems to be doing its job, and it wouldn't matter what way it was done.

                                             

                                            I think the performance though of in vs exists will also vary from engine to engine, so people might experience different results because to this.  It is also important to note the exclusion using "not in vs not exists vs left outer join" is another kettle of fish.

                                             

                                            Thanks for asking the question though, otherwise i wouldn't of even bothered looking into it again.

                                             

                                            Cheers

                                            • 19. Re: Making a join seems simple but I can't get it to work
                                              -==cfSearching==- Level 4

                                              I think the performance though of in vs exists will also

                                              vary from engine to engine, so people might experience

                                              different results because to this. 

                                               

                                              Yes, fair point. How well statements are optimized depends a lot on the database. On average, MS SQL tends to do a pretty good job of divining the intent of similar statements, and reducing them to the same execution plan. But not every database will behave the same. So reviewing the execution plans is probably the best approach.