Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Simple query with view returns too many records

Expand Messages
  • John Warner
    What are the prime keys on these two tables. My experience has been when I get duplicate records like this from a JOIN I m leaving out a key. John Warner ...
    Message 1 of 6 , Jun 9 7:44 AM
    • 0 Attachment
      What are the prime keys on these two tables. My experience has been when I
      get duplicate records like this from a JOIN I'm leaving out a key.

      John Warner


      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of radconentec
      > Sent: Monday, June 08, 2009 12:56 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: [SQLQueriesNoCode] Simple query with view returns too many
      > records
      >
      > Greetings!
      >
      > I'm working in PostgreSQL 8.3.
      >
      > A charge consists of a number of coils. Charges are listed in the
      charge table
      > and coils are listed in the inventory table.
      >
      > There is a set of functions that operate on coils. These functions are
      only
      > needed for a single Crystal Reports report, so instead of making them
      part of
      > the entire inventory structure, I created a new view to look at them.
      While
      > trying to track down this problem, I removed all of the functions and
      left myself
      > with a view that only returns the coil_id from the inventory table:
      >
      > CREATE OR REPLACE VIEW coilview AS
      > SELECT inventory.coil_id AS view_coil_id
      > FROM inventory;
      >
      > Now, I try using this view on a charge with two coils. First, I need to
      look in the
      > inventory table to find the coil_ids for the coils that are in the
      charge. Then, I
      > have to look in the coilview to get the view_coil_id. Since there's a
      one-to-one
      > correspondence between the inventory table and the view, I should get
      two
      > records back. (I'm pretty sure that this last sentence is wrong, but I
      don't know
      > why.) Instead, I get four records. Here's the query:
      >
      > select charge.charge, inventory.coil_id, view_coil_id
      > from charge
      > inner join inventory on charge.charge = inventory.charge
      > inner join coilview on inventory.coil_id = coilview.view_coil_id
      > where charge.charge = 4014
      >
      > I get two records for each coil, not just one record. Why?
      >
      > Thank you very much!
      >
      > RobR
      >
      >
      >
      > ------------------------------------
      >
      > Yahoo! Groups Links
      >
      >
      >
    • Radcon Entec
      I believe I have figured out the problem. It s a basic misunderstanding on my part. To oversimplify things significantly, when an inner join query is executed,
      Message 2 of 6 , Jun 9 7:57 AM
      • 0 Attachment
        I believe I have figured out the problem. It's a basic misunderstanding on my part. To oversimplify things significantly, when an inner join query is executed, all possible combinations of the two tables are calculated, and then any filter in the query is applied. And I am not joining two tables. I'm joining a table and a view on the same table.
         
        So, let's say I have a table X and column V. The table has 3 rows, with V = 1, 2 and 3. If, for some unknown reason, I try this query:
         
        SELECT X.V, X_copy.V
        FROM X
        INNER JOIN X X_Copy ON X.V = X_copy.V
         
        I should get nine records in the result set. Each of the three records will have its own link to each of the three records from the second scan of the table.
         
        Now, if I add a filter:
         
        SELECT X.V, X_copy.V
        FROM X
        INNER JOIN X X_Copy ON X.V = X_copy.V
        WHERE X.V = 1
         
        I won't get just a single row in the result set. I'll get 3.
         
        Right?
         
        Thanks to all who have replied so far, and to those who will reply to this.
         
        RobR





        [Non-text portions of this message have been removed]
      • Radcon Entec
        I believe I have figured out the problem. It s a basic misunderstanding on my part. To oversimplify things significantly, when an inner join query is executed,
        Message 3 of 6 , Jun 9 8:42 AM
        • 0 Attachment
          I believe I have figured out the problem. It's a basic misunderstanding on my part. To oversimplify things significantly, when an inner join query is executed, all possible combinations of the two tables are calculated, and then any filter in the query is applied. And I am not joining two tables. I'm joining a table and a view on the same table.
           
          So, let's say I have a table X and column V. The table has 3 rows, with V = 1, 2 and 3. If, for some unknown reason, I try this query:
           
          SELECT X.V, X_copy.V
          FROM X
          INNER JOIN X X_Copy ON X.V = X_copy.V
           
          I should get nine records in the result set. Each of the three records will have its own link to each of the three records from the second scan of the table.
           
          Now, if I add a filter:
           
          SELECT X.V, X_copy.V
          FROM X
          INNER JOIN X X_Copy ON X.V = X_copy.V
          WHERE X.V = 1
           
          I won't get just a single row in the result set. I'll get 3.
           
          Right?
           
          Thanks to all who have replied so far, and to those who will reply to this.
           
          RobR





          [Non-text portions of this message have been removed]
        • peternilsson42
          ... No, you should get 3 rows: 1 1, 2 2, 3 3. ... No, the join condition specifies otherwise. ... No. Your issue is that there are multiple coil_id values
          Message 4 of 6 , Jun 9 4:04 PM
          • 0 Attachment
            --- In SQLQueriesNoCode@yahoogroups.com, Radcon Entec <radconentec@...> wrote:
            > ...To oversimplify things significantly, when an inner
            > join query is executed, all possible combinations of the
            > two tables are calculated, and then any filter in the
            > query is applied. And I am not joining two tables. I'm
            > joining a table and a view on the same table.
            >
            > So, let's say I have a table X and column V. The table
            > has 3 rows, with V = 1, 2 and 3. If, for some unknown
            > reason, I try this query:
            >
            > SELECT X.V, X_copy.V
            > FROM X
            > INNER JOIN X X_Copy ON X.V = X_copy.V
            >
            > I should get nine records in the result set.

            No, you should get 3 rows: 1 1, 2 2, 3 3.

            > Each of the three records will have its own link to
            > each of the three records from the second scan of the
            > table.

            No, the join condition specifies otherwise.

            > Now, if I add a filter:
            >
            > SELECT X.V, X_copy.V
            > FROM X
            > INNER JOIN X X_Copy ON X.V = X_copy.V
            > WHERE X.V = 1
            >
            > I won't get just a single row in the result set. I'll get 3.
            >
            > Right?

            No. Your issue is that there are multiple coil_id values
            shared accross multiple charges.

            Take your original script...

            create or replace view coilview as
            select inventory.coil_id as view_coil_id
            from inventory;

            select charge.charge, inventory.coil_id, view_coil_id
            from charge
            join inventory on charge.charge = inventory.charge
            join coilview on inventory.coil_id = coilview.view_coil_id
            where charge.charge = 4014

            Suppose inventory has the rows...

            rownum charge coil_id
            i1 4014 1
            i2 4014 2
            i3 4000 2

            The view, will have the rows...

            rownum view_coil_id
            v1 1
            v2 2
            v3 2

            Now the join to coilview is purely on coil_id. Assuming
            charge is a primary key of charge, you get...

            rownum charge coil_id view_coil_id
            i1v1 4014 1 1
            i2v2 4014 2 2
            i2v3 4014 2 2

            Note that row v3 in coilview relates to a different charge,
            but you've removed the ability to distinguish that in your
            view.

            You haven't stated the full problem, but you may simply
            need a select distinct in coilview.

            --
            Peter
          • Radcon Entec
            Peter, Thank you very much for taking the time to respond in such detail. I understand now where my problem was coming from, and of course, it s my own
            Message 5 of 6 , Jun 10 6:30 AM
            • 0 Attachment
              Peter,

              Thank you very much for taking the time to respond in such detail.

              I understand now where my problem was coming from, and of course, it's my own fault.  One of your example tables was:

                rownum charge coil_id
                i1    4014  1
                i2    4014  2
                i3    4000  2

              My first thought was that this can't happen, because coil_id values are unique.  But then I remembered: coil_ids are not unique.  There is a field in the inventory table named "archived". All programs and database functions are designed to ensure that there will never be more than one inventory record with the same coil_id and archived = 0 at one time, but there is no constraint enforcing that rule.  (There are many places where there should be constraints but there aren't in our database.)  It is very common for customers to reuse coil IDs, as long as the previous use of that ID is for a coil that has long since left their system.  I created the database I'm using to test this report by copying a customer's database and setting archived = 0 for all charges and all coils.  Thus, I blew the "only 1 coil_id with archived = 0" rule out of the water.

              RobR





              [Non-text portions of this message have been removed]
            Your message has been successfully submitted and would be delivered to recipients shortly.