please help with joins

[tomasd]

Hi, I'm having 3 tables;

 

SQL> describe event_staff;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

STAFF_ID                                  NOT NULL NUMBER(38)

EVENT_ID                                  NOT NULL VARCHAR2(2)

 

SQL> describe event_staff_agy;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

STAFF_AGY_ID                              NOT NULL NUMBER(38)

EVENT_ID                                  NOT NULL VARCHAR2(2)

 

SQL> describe event;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

LOCATION                                  NOT NULL VARCHAR2(10)

ID                                        NOT NULL VARCHAR2(2)

CLIENT_ID                                NOT NULL NUMBER(38)

 

SQL> select * from event_staff;

 

  STAFF_ID EV

---------- --

        1 1

        2 1

 

SQL> select * from event_staff_agy;

 

STAFF_AGY_ID EV

------------ --

          1 1

          2 1

 

SQL> select * from event;

 

LOCATION  ID  CLIENT_ID

---------- -- ----------

Loc 1      1          1

Loc 2      2          1

 

 

I'm trying to join those tables using;

SQL> SELECT

        EVENT.ID,

        EVENT_STAFF.STAFF_ID,

        EVENT_STAFF_AGY.STAFF_AGY_ID

FROM(

(EVENT INNER JOIN EVENT_STAFF ON EVENT.ID = EVENT_STAFF.EVENT_ID)

INNER JOIN EVENT_STAFF_AGY ON EVENT.ID = EVENT_STAFF_AGY.EVENT_ID

);

  2    3    4    5    6    7    8

ID  STAFF_ID STAFF_AGY_ID

-- ---------- ------------

1          1            1

1          1            2

1          2            1

1          2            2

 

I guess this is not correct as the result I'm expecting is;

ID  STAFF_ID STAFF_AGY_ID

-- ---------- ------------

1          1           

1          2           

1                        1

1                        2

 

what would be the right way of joining this?

Any help is appreciated!

[fenway]

You've joined it correctly... not sure why you were expecting the second set of results.

[Barand]

To get the results you expect you would need a UNION

SELECT
        EVENT.ID,
        EVENT_STAFF.STAFF_ID,
        '' AS STAFF_AGY_ID
FROM
EVENT INNER JOIN EVENT_STAFF ON EVENT.ID = EVENT_STAFF.EVENT_ID
UNION
SELECT
        EVENT.ID,
        '' AS STAFF_ID,
        EVENT_STAFF_AGY.STAFF_AGY_ID
FROM
EVENT
INNER JOIN EVENT_STAFF_AGY ON EVENT.ID = EVENT_STAFF_AGY.EVENT_ID