CODEWITHSUNDEEP
sql
Yaganti Ashok Chakravarthy
Yaganti Ashok Chakravarthy

Reputation: 21

Select columns from two tables

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

Upvotes: 0

Views: 90

Answers (2)

forpas
forpas

Reputation: 164089

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 |

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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;

Upvotes: 2

Related Questions