Home » SQL & PL/SQL » SQL & PL/SQL » ANSI SQL Correlation Problem
ANSI SQL Correlation Problem [message #336457] Sun, 27 July 2008 03:34 Go to next message
gauss
Messages: 7
Registered: March 2005
Junior Member
Hi I've got a problem I'm trying to solve in one query.

TABLE_A

SERIAL
TIMESTAMP
STATUS
USER


TABLE_B

SERIAL
TIMESTAMP
ACTION

Table A contains a start and end record, table B contains records for any actions taken. The records are identified by being between the start and end timestamps and having the same serial.

The serial is not unique across the table but is unique for a given timestamp.

TABLE_A

SERIAL | TIMESTAMP | STATUS | USER
01 | 2008-02-16 09:38:27 | Start | John
02 | 2008-02-16 09:39:34 | Start | James
01 | 2008-02-16 11:16:10 | End | John
01 | 2008-02-16 11:32:28 | Start | Bill


TABLE_B

SERIAL | TIMESTAMP | ACTION
01 | 2008-02-16 09:38:34 | 4
01 | 2008-02-16 09:38:50 | 6
02 | 2008-02-16 09:42:15 | 2
01 | 2008-02-16 11:38:27 | 4
01 | 2008-02-16 11:39:33 | 5

What I'd like is the following, joining the 2 tables with all records from B being assigned to the user that was active at that time with the same serial number. Something like this...

SERIAL | TIMESTAMP | USER | ACTION
01 | 2008-02-16 09:38:34 | John | 4
01 | 2008-02-16 09:38:50 | John | 6
02 | 2008-02-16 09:42:15 | James | 2
01 | 2008-02-16 11:38:27 | Bill | 4
01 | 2008-02-16 11:39:33 | Bill | 5

I've been playing around doing this with intermediate tables but would like to do it in one query if possible and only using ansi sql!
Re: ANSI SQL Correlation Problem [message #336461 is a reply to message #336457] Sun, 27 July 2008 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are the primary key for each table?
Post a test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: ANSI SQL Correlation Problem [message #336463 is a reply to message #336461] Sun, 27 July 2008 04:56 Go to previous messageGo to next message
gauss
Messages: 7
Registered: March 2005
Junior Member
That's essentially the issue! There is no pk so joining the tables requires joining each row from table b to table a on serial where the timestamp of b is between the start and end timestamps of table a for the same serial.
Re: ANSI SQL Correlation Problem [message #336464 is a reply to message #336463] Sun, 27 July 2008 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't mean PK to join, but your table has surely PK otherwise your problem have no solution, SQL or not.

Test case still needed.

Regards
Michel
Re: ANSI SQL Correlation Problem [message #336465 is a reply to message #336464] Sun, 27 July 2008 05:03 Go to previous messageGo to next message
gauss
Messages: 7
Registered: March 2005
Junior Member
PK would be a composite of of serial and timestamp, same for both tables.

Not sure I understand what else you'd like? I've given the data and expected results?
Re: ANSI SQL Correlation Problem [message #336466 is a reply to message #336465] Sun, 27 July 2008 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
create table and insert statements along with the result you want with these data.

Regards
Michel
Re: ANSI SQL Correlation Problem [message #336467 is a reply to message #336457] Sun, 27 July 2008 09:37 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You can select the serial number, timestamp, and user from table_a where the status is start and use the lead analytic function to also obtain the timestamp where the status is end. Then you can do a join of that result set to table_b where the serial numbers are equal and table_b's timestamp is greater than or equal to the start timestamp and table_b's timestamp is less than or equal to the end timestamp or the end timestamp is null, and get the action.
Previous Topic: UTL_FILE
Next Topic: query problem
Goto Forum:
  


Current Time: Sat Dec 10 12:45:39 CST 2016

Total time taken to generate the page: 0.06454 seconds