Problem querying a view

From: teresa <pixelmeow_at_gmail.com>
Date: Mon, 11 Jun 2007 18:44:03 -0000
Message-ID: <1181587443.989753.48550@c77g2000hse.googlegroups.com>

Hello all,

I have inherited a database application and I'm writing views based on the queries the application uses. One of the queries is a join on three tables to get a count of a particular user id. Here are the tables (examples):

CREATE TABLE TA
(ID NUMBER, TYPE VARCHAR2(1 BYTE), USER_ID VARCHAR2(50 BYTE)) CREATE TABLE TC
(ID NUMBER, INST_ID NUMBER) CREATE TABLE TI
(ABBR VARCHAR2(20 BYTE), INST_NAME VARCHAR2(100 BYTE), INST_ID NUMBER) Here is the view:

CREATE OR REPLACE FORCE VIEW TA_C_I AS
  SELECT

         a.id AS a_id


        ,a.type AS type

        ,a.user_id AS user_id

        ,c.id AS c_id

        ,c.inst_id AS c_inst_id

        ,i.abbr AS abbr

        ,i.inst_name AS inst_name

        ,i.inst_id AS i_inst_id

    FROM
         TA a


        ,TC c

        ,TI i

WHERE a.id = c.id
AND c.inst_id = i.inst_id

When I query:
SQL> select count(*), user_id

  2 from ta_c_i
  3 group by user_id;

  COUNT(*) USER_ID

---------- --------------------------------------------------
       100 8E1E9E08-802E-D019-AF4C32C5CF0D1BB2
...
       121 A0725615-802E-D019-A6B3168A70F3C2F2

47 rows selected.

Then when I query:
SQL> select count(*) from ta_c_i
  2 where user_id = 'A0725615-802E-D019-A6B3168A70F3C2F2';

  COUNT(*)

---

         0

I must not have a good idea of how this works (probable). I really did expect the second query to return 121, since that was the count from the last row of the previous query, where I got the user_id from. (the view's query works in the production system, which isn't different from what I'm working with now)

Why didn't the second query return 121?

Thanks so much,
teresa Received on Mon Jun 11 2007 - 13:44:03 CDT