Re: Correlated query in 8i

Likely the problem is in the relationship between the two tables. Cut the complex query and go with something simple so you can see what is really happening. Perhaps something with just the primary keys from each table.

Possibly the solution may involve using an in-line view to clean up the data. and then joining one of the tables to the in-line view. But without seeing the data ... this is only a speculation.

Daniel Morgan

Sergiu gp wrote:

> Hello guys,
>
> My prob_rep_tbl has a column "severity" where the number 1 occurs 5 times, as I
> look at the data in the table. When I am trying to run a query on that table
> and others related to find out how many occurences of severity "1" I keep
> getting 8 rows. Here is my query
>
> SELECT distinct last_name || ', ' || first_name as "Owner's Name",
> '(' || SUBSTR(phone, 1, 3) || ') ' || SUBSTR(phone, 4, 3) || '-' ||
> SUBSTR(phone, 7, 4) || ' ' as "Phone Number",
> serial_number as "Serial Num", prob_rep_tbl.model_id as "Model",
> TO_CHAR(purchase_date, 'fmMonth DD, YYYY') as "Purchase Date"
> FROM purchase_tbl, prob_rep_tbl, owner_tbl
> WHERE purchase_tbl.owner_id = owner_tbl.owner_id
> AND purchase_tbl.model_id = prob_rep_tbl.model_id
> AND purchase_tbl.model_id in
> (SELECT model_id
> FROm prob_rep_tbl
> WHERE severity =1)
>
> Thank you kindly,
>
> Sergiu
Received on Mon Feb 25 2002 - 02:52:53 CET