CODEWITHSUNDEEP
sqlsql-servert-sqljoin
Oleksiy Kustlyvy
Oleksiy Kustlyvy

Reputation: 25

sql one-to-one join / match

hello I need solution for this kind of problem:

sample: tblPeople:

 - id   Value  carColor
 1. Pid1  boy1  blue
 2. Pid2  boy2  blue
 3. Pid3  boy3  blue
 4. Pid4  girl1 red
 5. Pid5  girl2 red

tblCars:

 - id   Value
 1. Cid1  blue
 2. Cid2  blue
 3. Cid3  red
 4. Cid4  red
 5. Cid5  red

condition: - boys can use only blue cars but girls - red only - people can use 1 car only, car fits 1 person only

distribute cars between people

expected result(s): 

 1. Pid1  boy1 Cid1 blue
 2. Pid2  boy2 Cid2 blue
 3. Pid3  boy3 (no enough blue car - skipping)
 4. Pid4  girl1 Cid3 red
 5. Pid5  girl2 Cid4 red

or 

 1. Pid1  boy1 Cid2 blue
 2. Pid2  boy2 Cid1 blue
 3. Pid3  boy3 (no enough blue car - skipping)
 4. Pid4  girl1 Cid5 red
 5. Pid5  girl2 Cid3 red

for now I'm using select top 1 on join in cycle with marking chosen records to remove it in next matching, but it takes lot of time ...

can anyone help with it? thanks

Upvotes: 2

Views: 108

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can do this by enumerating the cars and enumerating the people, and then doing a join:

select p.*, c.*
from (select p.*,
             row_number() over (partition by gender order by gender) as seqnum
      from tblPeople p
     ) p left join
     (select c.*,
             row_number() over (partition by color order by color) as seqnum
      from cars c
     ) c
     on p.seqnum = c.seqnum and
        ( (p.gender = 'boy' and c.color = 'blue') or
          (p.gender = 'girl' and c.color = 'red')
        ) ;

This should be much, much faster than your current method.

Note: You can use newid() for ordering if you want to randomize the assignment each time you run the query.

Upvotes: 1

Related Questions