| Help required for SQL query to fetch desired records [message #623497] |
Wed, 10 September 2014 18:48  |
 |
gupta27
Messages: 31
Registered: August 2013
Location: Delhi
|
Member |
|
|
I have three tables policy_tbl, txn_tbl, price_tbl
structure of these tables are as follows:
create table policy_tbl (
policy_id varchar2(5) not null,
state char(2), not null );
policy_id is the primary key in policy_tbl and have foriegn key relationship with txn_tbl.
create table txn_tbl (
Policy_id varchar2(5) not null,
txn_id number(5) not null );
txn_id is primary key in txn_tbl and have foreign key relationship with price_tbl.
create price_tbl (
txn_id number(5) not null,
price number(3) not null,
state char(2) not null );
From policy_tbl - First I want to fetch policy_ids for which state is not in 'VA' (you can take any state). Then using this policy_id I want to select the max(txn_id) from txn_table (txn_table would have multiple txns corresponding to one policy_id).
Using this txn_id , I want to fetch rows from price_tbl which does not have unique txd_id (multiple records corresponding to one txn_id).
I could write the below query but not sure about how to select only those records from price_tbl which have multiple records corresponding to one txn_id.
select * from price_tbl pr
where pr.txn_id = t.txn_id
and pr.txn_id =
select max(t.txn_id) from txn_table t
where t.policy_id =(
select p.policy_id from policy_tbl p
where p.state != 'VA'
and p.policy_id= t.policy_id);
Please help.
|
|
|
|
|
|
|
|
|
|
| Re: Help required for SQL query to fetch desired records [message #623501 is a reply to message #623500] |
Wed, 10 September 2014 18:59  |
|
gupta27
Messages: 31
Registered: August 2013
Location: Delhi
|
Member |
|
|
sorry that was by mistake...wanted to write 1 only...let me write the query again:
select * from price_tbl pr
where pr.txn_id = t.txn_id
and pr.txn_id =
( select max(t.txn_id) from txn_table t
where t.policy_id =(
select p.policy_id from policy_tbl p
where p.state != 'VA'
and p.policy_id= t.policy_id))
group by pr.txn_id having count(txn_id) > 10 ;
[Updated on: Wed, 10 September 2014 19:01] Report message to a moderator |
|
|
|
|
|
| Re: Help required for SQL query to fetch desired records [message #623503 is a reply to message #623502] |
Wed, 10 September 2014 19:14 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Personally I am not a fan of co-related queries, I try to convert them into join.
You may try below:
CREATE TABLE policy_tbl
(
policy_id VARCHAR2 (5) NOT NULL,
state CHAR (2) NOT NULL
);
CREATE TABLE txn_tbl
(
Policy_id VARCHAR2 (5) NOT NULL,
txn_id NUMBER (5) NOT NULL
);
CREATE TABLE price_tbl
(
txn_id NUMBER (5) NOT NULL,
price NUMBER (3) NOT NULL,
state CHAR (2) NOT NULL
);
WITH temp AS (SELECT MAX (t.txn_id) txn_id
FROM txn_tbl t, policy_tbl p
WHERE t.policy_id = p.policy_id AND p.state != 'VA')
SELECT pr.txn_id, COUNT (*)
FROM price_tbl pr, temp t
WHERE pr.txn_id = t.txn_id
GROUP BY pr.txn_id
HAVING COUNT (*) > 1;
Manu
[Updated on: Wed, 10 September 2014 19:15] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
