CODEWITHSUNDEEP
sqloracle-database
aipeursson
aipeursson

Reputation: 15

Is it possible to join two tables of multiple rows by only the first ID in each table?

Let's say I have two tables of data:

Table A
ID    Colors
--    ------
1     Blue
1     Green
1     Red

Table B
ID    States
--    ------
1     MD
1     VA
1     WY
1     CA

Is it possible to join these tables so that I get the following instead of 12 rows?

ID    Colors    States
--    ------    ------
1     Blue      MD
1     Green     VA
1     Red       WY
1               CA

There's no association between the colors and states columns and the order of the columns doesn't matter. (e.g. Blue can be next to MD, VA, WY, or CA) The number of items in each column (Colors or States) per ID is not equal.

Thanks.

Upvotes: 0

Views: 90

Answers (2)

BRogers
BRogers

Reputation: 3624

You can also use a CTE (Common Table Expression) Like so:

WITH TableA (ID, Color) AS
(
    SELECT "ID", Color 
    FROM DatabaseName.TableA
)
, Joined AS (
    SELECT 
        a.ID AS "AID",
        a.Color
        b.ID AS "BID", 
        b."State"
    FROM, 
        TableA AS a
            RIGHT OUTER JOIN DatabaseName.TableB AS b ON a.ID = b.ID
)

SELECT
    AID,
    Color,
    State
FROM
    Joined

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can do this by using row_number() to create a fake join column:

select coalesce(a.id, b.id) as id, a.colors, b.states
from (select a.*, row_number() over (order by id) as seqnum
      from a
     ) a full outer join
     (select b.*, row_number() over (order by id) as seqnum
      from b
     ) b
     on b.seqnum = a.seqnum

Actually, in Oracle, you can also just use rownum:

select coalesce(a.id, b.id) as id, a.colors, b.states
from (select a.*, rownum as seqnum
      from a
     ) a full outer join
     (select b.*, rownum as seqnum
      from b
     ) b
     on b.seqnum = a.seqnum

Upvotes: 1

Related Questions