Select columns from two tables

Question

Table A:
AID type
10      a
10      b
10      c
11      abc
11      bcd
11      cde

Table B
BID Name
10      ab
10      bc
10      cd
11      abcd
11      bcde
11      cdef

Result Should be

AID type Name
10      a      ab
10      b      bc
10      c      cd
11      abc      abcd
11      bcd      bcde
11      cde      cdef

could you please help me on this for the SQL query

Hint: join table A and B using ID column and select the required columns — BlackSwan, Apr 27 '19 at 17:51
https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins/20298671 — OldProgrammer, Apr 27 '19 at 17:53
No this will not work. If I use join by ID, I will get 18 rows — Yaganti Ashok Chakravarthy, Apr 27 '19 at 17:54

score 2 · Accepted Answer · answered Apr 27 '19 at 17:53

You are trying to align two tables, but do not have a key. You can generate a key using row_number() and then match. Just note that you cannot control which values from the two tables match, unless you have another column that specifies the ordering.

So:

select a.id, a.name, b.name
from (select a.*, row_number() over (partition by aid order by name) as seqnum
      from a
     ) a join
     (select b.*, row_number() over (partition by bid order by name) as seqnum
      from b
     ) b
     on a.aid = b.bid and a.seqnum = b.seqnum;

remove `partition by aid` & `partition by bid` from the statement — Michael Z., Apr 27 '19 at 18:06

forpas · Answer 2 · 2019-04-27T18:22:19.957

You can do it with this join:

select a.aid, a.type, b.name
from TableA a inner join TableB b
on b.bid = a.aid and b.name like concat(a.type, '%')

this will give you the 6 rows from TableA with name from TableB as 3d column.
See the demo.
Results:

| aid | type | name |
| --- | ---- | ---- |
| 10  | a    | ab   |
| 10  | b    | bc   |
| 10  | c    | cd   |
| 11  | abc  | abcd |
| 11  | bcd  | bcde |
| 11  | cde  | cdef |

Select columns from two tables

2 Answers2