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

Simple query with view returns too many records

Expand Messages
  • radconentec
    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
    Message 1 of 6 , Jun 8, 2009
    • 0 Attachment
      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
    • 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 2 of 6 , Jun 9, 2009
      • 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 3 of 6 , Jun 9, 2009
        • 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 4 of 6 , Jun 9, 2009
          • 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 5 of 6 , Jun 9, 2009
            • 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 6 of 6 , Jun 10, 2009
              • 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.