Home » SQL & PL/SQL » SQL & PL/SQL » Select one table base on value of another table (oracle 10g)
Select one table base on value of another table [message #330983] Wed, 02 July 2008 00:47 Go to next message
tmlaio
Messages: 6
Registered: September 2006
Junior Member
I have a user table and a transaction table.
User table have the following sample data:
user_id  start_date  end_date
-------  ----------  --------
1        1-Apr-2007  31-Mar-2008
1        1-Jun-2008  31-Dec-2008
2        1-Jan-2008  31-Dec-2008
...

Transaction table have the following sample data:
tran_id  tran_date
-------  ---------
100      1-Mar-2008
101      4-Mar-2008
102      15-Apr-2008
103      12-May-2008
104      6-Jun-2008
...

User table control which user can access to transactions of which period. The same user will not have overlapped periods.
The problem is user can enquiry on transactions within a specifed date range. We have to list all the relevant transactions but exclude those not accessible by the user.
For example, user with id 1, enquiry transactions between 1-Feb-2008 and 30-Jun-2008, we should only return transaction ids 100, 101 and 104, excluding 102 and 103.
How should I construct the sql, please help. Thanks in advance.
Re: Select one table base on value of another table [message #330984 is a reply to message #330983] Wed, 02 July 2008 00:49 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guidelines below
http://www.orafaq.com/forum/t/88153/0/
Re: Select one table base on value of another table [message #330986 is a reply to message #330983] Wed, 02 July 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a clue: restrict to rows where it exists a user period where transactioin date is between start and end dates.

Regards
Michel
Re: Select one table base on value of another table [message #331156 is a reply to message #330986] Wed, 02 July 2008 07:33 Go to previous messageGo to next message
tmlaio
Messages: 6
Registered: September 2006
Junior Member
Thank you Michel.

I think it is someting like this:
select *
from transaction
where tran_date between ? and ?
and exists (
select *
from user
where user_id = ?
and tran_date between start_date and end_date);


Thanks a lot.
Re: Select one table base on value of another table [message #331168 is a reply to message #331156] Wed, 02 July 2008 08:15 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That's correct.

Regards
Michel
Previous Topic: sequence gap (merged)
Next Topic: query for count
Goto Forum:
  


Current Time: Tue Dec 06 00:26:19 CST 2016

Total time taken to generate the page: 0.08983 seconds