Home » SQL & PL/SQL » SQL & PL/SQL » ANSI SQL Correlation Problem
ANSI SQL Correlation Problem Sun, 27 July 2008 03:34
 gauss Messages: 7Registered: 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
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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.

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
 gauss Messages: 7Registered: 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
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 gauss Messages: 7Registered: 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
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Barbara Boehmer Messages: 8737Registered: 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: Can we write two "Insert into.." on a single table
Goto Forum:

Current Time: Sun Aug 20 20:01:01 CDT 2017

Total time taken to generate the page: 0.06498 seconds