CODEWITHSUNDEEP
sqljoin
Jacek Średziński
Jacek Średziński

Reputation: 11

How to make a join in order to get this result

I have problem joining two tables to get result as in Table_C.

How do I join these tables table_A and table_B to get something like table_C

table_A

name    option  sex
Ann     A       K
Cezar   B       M
Martha  C       K

table_B

Room        option
pool        A
river       A
gym         B
slide       B
spa         C
sauna       C

table_C

name    option  sex Room
Ann     A       F   Pool
Ann     A       F   River
Cezar   B       M   Pool
Cezar   B       M   River
Cezar   B       M   Gym
Cezar   B       M   Slide
Martha  C       F   Pool
Martha  C       F   River
Martha  C       F   Gym
Martha  C       F   Slide
Martha  C       F   Spa
Martha  C       F   Sauna

Upvotes: 1

Views: 41

Answers (3)

LukStorms
LukStorms

Reputation: 29667

Gordon's solution will work fine for that sample data.
Since A < B < C

But if it needs to more explicit:

select a.name, b.[option], a.sex, b.Room
from table_A a 
join table_B b
     on b.[option] = a.[option] -- include same option
     or b.[option] = 'A'  -- always include option A rooms
     or (b.[option] = 'B' and a.[option] = 'C')

Upvotes: 0

Mohammad Shehroz
Mohammad Shehroz

Reputation: 236

what output you want here is your query :)

create table table_A (name varchar(10),[option] varchar(1),sex varchar(1))
create table table_B(Room varchar(10),[option] varchar(1))

insert into table_A VAlues ('Ann', 'A','K')
insert into table_A VAlues ('Cezar','B', 'M')
insert into table_A VAlues ('Martha' ,'C'  ,'K')

insert into table_B values('pool '       ,'A')
insert into table_B values('river'       ,'A')
insert into table_B values('gym  '       ,'B')
insert into table_B values('slide'       ,'B')
insert into table_B values('spa  '       ,'C')
insert into table_B values('sauna'       ,'C')



select a.[name],a.[option],a.sex,b.Room from table_A A
INNER join table_B b on a.[option] >= b.[option]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use inequalities in JOINs. That seems to be what you want:

select a.*, b.*
from table_A a join
     table_B b
     on b.option <= a.option

Upvotes: 1

Related Questions