Hello,

I would like to join two tables sequentially (I don't know the right
term for this; I try to explain below). I am a programmer, but
inexperienced with sql; I'd appreciate it if someone could help me out.

I have a "schedule" table (which we can assume has already been sorted
by the schedule id) with a site id number and a value like:

site_id type
101 A
101 B
101 B
102 C
102 B
102 B
103 A
103 A

and I want to join it with an "objects" table, which has a link to a
site, like:

obj_id site_id
1 101
2 102
3 101
4 101
5 102
6 103
7 103

How would I join them such that the first object with site_id of 101 is
joined with the type of the first schedule item with site_id of 101,
the second object of site 101 with the second schedule item from site
101, etc. in sequential order.

The result I want looks like:

obj_id type site_id
1 A 101
2 C 102
3 B 101
4 B 101
5 B 102
6 A 103
7 A 103

Do I need to use PL/SQL to achieve this? I feel like I don't, but
neither can I figure out how to "iterate" through the schedule table
without producing repeats. I can think of all kinds of ugly
programmatic ways to do it, but I feel like I'm missing an "aha" moment
for a SQL solution.
Any help would be appreciated.

Peace
Bill Mill
bill.mill at gmail.com

On 21 Dec 2004 11:47:49 -0800, "Bill Mill" <bill.mill@gmail.com>
wrote:

>The result I want looks like:
>
>obj_id type site_id
>1 A 101
>2 C 102
>3 B 101
>4 B 101
>5 B 102
>6 A 103
>7 A 103
>
>Do I need to use PL/SQL to achieve this?

You would, unless you redesign your tables. Your problem is not so
much lack of SQL knowledge, but you are also lacking on fundamentals
of relational databases. Relational databases are about sets. A set,
as a mathematic concept, does not have any order.
Consequently B 101 doesn't come 'after' A 101, and you are assuming
there is a specific order which in reality doesn't exist at all, or
won't exist anymore once you start inserting and updating records.
Your thinking is clearly 3GL-oriented.
So your's the choice:
either you rectify your design, by adding an ordering number to your
table, or
you start hacking yourself out with PL/SQL


--
Sybrand Bakker, Senior Oracle DBA

Sybrand,

I should have been clearer. B 101 does come 'after' A 101, but I didn't
include that information here to try and simplify things. The
"schedule" table has an id field which determines its order, but I did
not include it in my post, since I didn't think it was directly
relevant to the join.

I tried to say that parenthetically, but was unclear about it.
Peace
Bill Mill
bill.mill at gmail.com

In article <1103658469.907129.255490@f14g2000cwb.googlegroups .com>, Bill Mill
says...
>
>Hello,
>
>I would like to join two tables sequentially (I don't know the right
>term for this; I try to explain below). I am a programmer, but
>inexperienced with sql; I'd appreciate it if someone could help me out.
>
>I have a "schedule" table (which we can assume has already been sorted
>by the schedule id) with a site id number and a value like:
>
>site_id type
>101 A
>101 B
>101 B
>102 C
>102 B
>102 B
>103 A
>103 A
>
>and I want to join it with an "objects" table, which has a link to a
>site, like:
>
>obj_id site_id
>1 101
>2 102
>3 101
>4 101
>5 102
>6 103
>7 103
>
>How would I join them such that the first object with site_id of 101 is
>joined with the type of the first schedule item with site_id of 101,
>the second object of site 101 with the second schedule item from site
>101, etc. in sequential order.
>
>The result I want looks like:
>
>obj_id type site_id
>1 A 101
>2 C 102
>3 B 101
>4 B 101
>5 B 102
>6 A 103
>7 A 103
>
>Do I need to use PL/SQL to achieve this? I feel like I don't, but
>neither can I figure out how to "iterate" through the schedule table
>without producing repeats. I can think of all kinds of ugly
>programmatic ways to do it, but I feel like I'm missing an "aha" moment
>for a SQL solution.
>Any help would be appreciated.
>
>Peace
>Bill Mill
>bill.mill at gmail.com
>

wouldn't want to get carried away on the number of rows due to the amount of
sorting that has to happen, but....



ops$tkyte@ORA9IR2> select site_id, type,
2 row_number() over (partition by site_id order by schedule_id) rn
3 from schedule
4 order by 1, 3
5 /

SITE_ID T RN
---------- - ----------
101 A 1
101 B 2
101 B 3
102 C 1
102 B 2
102 B 3
103 A 1
103 A 2

8 rows selected.

ops$tkyte@ORA9IR2> select obj_id, site_id,
2 row_number() over(partition by site_id order by obj_id) rn
3 from objects
4 order by 2, 3
5 /

OBJ_ID SITE_ID RN
---------- ---------- ----------
1 101 1
3 101 2
4 101 3
2 102 1
5 102 2
6 103 1
7 103 2

7 rows selected.


ops$tkyte@ORA9IR2> select b.obj_id, a.type, b.site_id
2 from (
3 select site_id, type,
4 row_number() over (partition by site_id order by schedule_id) rn
5 from schedule
6 ) A,
7 (
8 select obj_id, site_id,
9 row_number() over(partition by site_id order by obj_id) rn
10 from objects
11 ) B
12 where a.site_id = b.site_id
13 and a.rn = b.rn
14 order by b.obj_id
15 /

OBJ_ID T SITE_ID
---------- - ----------
1 A 101
2 C 102
3 B 101
4 B 101
5 B 102
6 A 103
7 A 103

7 rows selected.


--
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

CREATE TABLE SCHEDULE(SITE_ID NUMBER, TYPE CHAR(1));
INSERT INTO SCHEDULE VALUES(101, 'A');
INSERT INTO SCHEDULE VALUES(101, 'B');
INSERT INTO SCHEDULE VALUES(101, 'B');
INSERT INTO SCHEDULE VALUES(102, 'B');
INSERT INTO SCHEDULE VALUES(102, 'C');
CREATE TABLE OBJECTS(OBJECT_ID NUMBER, SITE_ID NUMBER);
INSERT INTO OBJECTS VALUES(1, 101);
INSERT INTO OBJECTS VALUES(3, 101);
INSERT INTO OBJECTS VALUES(4, 101);
INSERT INTO OBJECTS VALUES(2, 102);
INSERT INTO OBJECTS VALUES(5, 102);

SELECT OBJECTS_TE.OBJECT_ID, SCHEDULE_TE.SITE_ID, SCHEDULE_TE.TYPE
FROM
(SELECT SITE_ID, TYPE, ROW_NUMBER() OVER(PARTITION BY SITE_ID ORDER BY
TYPE) RN FROM SCHEDULE) SCHEDULE_TE
JOIN
(SELECT OBJECT_ID, SITE_ID, ROW_NUMBER() OVER(PARTITION BY SITE_ID
ORDER BY OBJECT_ID) RN FROM OBJECTS) OBJECTS_TE
ON SCHEDULE_TE.SITE_ID = OBJECTS_TE.SITE_ID
AND SCHEDULE_TE.RN = OBJECTS_TE.RN;

OBJECT_ID SITE_ID TYPE
---------- ---------- ----
1 101 A
3 101 B
4 101 B
2 102 B
5 102 C
5 rows selected

DROP TABLE SCHEDULE;
DROP TABLE OBJECTS;

Tom,

1) You rock. Thank you very much.

2) OK, that is not simple. This response has taken a while because I'm
still trying to grok what's going on. I am 90% certain that you nailed
what I was asking. You gave me a lot to chew on.
3) You rock.

Peace
Bill Mill
bill.mill at gmail.com

Using OLAP functions, a piece of cake:

CREATE TABLE SCHEDULE(SITE_ID NUMBER, TYPE CHAR(1));
INSERT INTO SCHEDULE VALUES(101, 'A');
INSERT INTO SCHEDULE VALUES(101, 'B');
INSERT INTO SCHEDULE VALUES(101, 'B');
INSERT INTO SCHEDULE VALUES(102, 'B');
INSERT INTO SCHEDULE VALUES(102, 'C');
CREATE TABLE OBJECTS(OBJECT_ID NUMBER, SITE_ID NUMBER);
INSERT INTO OBJECTS VALUES(1, 101);
INSERT INTO OBJECTS VALUES(3, 101);
INSERT INTO OBJECTS VALUES(4, 101);
INSERT INTO OBJECTS VALUES(2, 102);
INSERT INTO OBJECTS VALUES(5, 102);

SELECT OBJECTS_TE.OBJECT_ID, SCHEDULE_TE.SITE_ID, SCHEDULE_TE.TYPE
FROM
(SELECT SITE_ID, TYPE, ROW_NUMBER() OVER(PARTITION BY SITE_ID ORDER BY
TYPE) RN FROM SCHEDULE) SCHEDULE_TE
JOIN
(SELECT OBJECT_ID, SITE_ID, ROW_NUMBER() OVER(PARTITION BY SITE_ID
ORDER BY OBJECT_ID) RN FROM OBJECTS) OBJECTS_TE
ON SCHEDULE_TE.SITE_ID = OBJECTS_TE.SITE_ID
AND SCHEDULE_TE.RN = OBJECTS_TE.RN;


OBJECT_ID SITE_ID TYPE
---------- ---------- ----
1 101 A
3 101 B
4 101 B
2 102 B
5 102 C
5 rows selected



DROP TABLE SCHEDULE;
DROP TABLE OBJECTS;

Using OLAP functions, a piece of cake:

CREATE TABLE SCHEDULE(SITE_ID NUMBER, TYPE CHAR(1));
INSERT INTO SCHEDULE VALUES(101, 'A');
INSERT INTO SCHEDULE VALUES(101, 'B');
INSERT INTO SCHEDULE VALUES(101, 'B');
INSERT INTO SCHEDULE VALUES(102, 'B');
INSERT INTO SCHEDULE VALUES(102, 'C');
CREATE TABLE OBJECTS(OBJECT_ID NUMBER, SITE_ID NUMBER);
INSERT INTO OBJECTS VALUES(1, 101);
INSERT INTO OBJECTS VALUES(3, 101);
INSERT INTO OBJECTS VALUES(4, 101);
INSERT INTO OBJECTS VALUES(2, 102);
INSERT INTO OBJECTS VALUES(5, 102);

SELECT OBJECTS_TE.OBJECT_ID, SCHEDULE_TE.SITE_ID, SCHEDULE_TE.TYPE
FROM
(SELECT SITE_ID, TYPE, ROW_NUMBER() OVER(PARTITION BY SITE_ID ORDER BY
TYPE) RN FROM SCHEDULE) SCHEDULE_TE
JOIN
(SELECT OBJECT_ID, SITE_ID, ROW_NUMBER() OVER(PARTITION BY SITE_ID
ORDER BY OBJECT_ID) RN FROM OBJECTS) OBJECTS_TE
ON SCHEDULE_TE.SITE_ID = OBJECTS_TE.SITE_ID
AND SCHEDULE_TE.RN = OBJECTS_TE.RN;


OBJECT_ID SITE_ID TYPE
---------- ---------- ----
1 101 A
3 101 B
4 101 B
2 102 B
5 102 C
5 rows selected


DROP TABLE SCHEDULE;
DROP TABLE OBJECTS;

CREATE TABLE SCHEDULE(SITE_ID NUMBER, TYPE CHAR(1));
INSERT INTO SCHEDULE VALUES(101, 'A');
INSERT INTO SCHEDULE VALUES(101, 'B');
INSERT INTO SCHEDULE VALUES(101, 'B');
INSERT INTO SCHEDULE VALUES(102, 'B');
INSERT INTO SCHEDULE VALUES(102, 'C');
CREATE TABLE OBJECTS(OBJECT_ID NUMBER, SITE_ID NUMBER);
INSERT INTO OBJECTS VALUES(1, 101);
INSERT INTO OBJECTS VALUES(3, 101);
INSERT INTO OBJECTS VALUES(4, 101);
INSERT INTO OBJECTS VALUES(2, 102);
INSERT INTO OBJECTS VALUES(5, 102);

SELECT OBJECTS_TE.OBJECT_ID, SCHEDULE_TE.SITE_ID, SCHEDULE_TE.TYPE
FROM
(SELECT SITE_ID, TYPE, ROW_NUMBER() OVER(PARTITION BY SITE_ID ORDER BY
TYPE) RN FROM SCHEDULE) SCHEDULE_TE
JOIN
(SELECT OBJECT_ID, SITE_ID, ROW_NUMBER() OVER(PARTITION BY SITE_ID
ORDER BY OBJECT_ID) RN FROM OBJECTS) OBJECTS_TE
ON SCHEDULE_TE.SITE_ID = OBJECTS_TE.SITE_ID
AND SCHEDULE_TE.RN = OBJECTS_TE.RN;

OBJECT_ID SITE_ID TYPE
---------- ---------- ----
1 101 A
3 101 B
4 101 B
2 102 B
5 102 C
5 rows selected

DROP TABLE SCHEDULE;
DROP TABLE OBJECTS;