Join record between table only once in MS Access

1
votes

I have tbl1 Structured like this

Name         Type
======        =====
John             1
David            1
Jane             2
William          3
Alex             2
Ryan             1

And tbl2 structured like this

Index   Type     Job
  1       1      Clean
  2       1      Wash
  3       2      Carry
  4       2      Package
  5       3      Sell

I would like to join record with matched Type, but each record in tbl1 only join once with one record in tbl2

Ie:

If John is joined with Clean then David must be joined with Wash. Or if John is joined with Wash then David must be joined with Clean.

Doesn't matter if David is joined with Wash or Clean , I only need them to be joined with record that match the criteria and be joined ONCE.

I will make sure for each Type in 'tbl1' there will be equivalent amount of record in 'tbl2'

I mainly work on MS Access so Query on this environment would be the best~ Thank you all for reading.

Best regards

1
sqlms-accessjoin
There seems to be a redundant record in tbl1. What do you want to do with this record? – Giorgos Betsos
If this is not Sql Server then remove the tag. – Rachel Ambler
You also have "Ryan" with type=1 in tbl1. So, what is expected relations for that row with tbl2? – Oto Shavadze
Yes for that if it can display Null for "Ryan" it's the best, but otherwise I would go to make sure there is another type=1 Record in tbl2 to match with the amount in tbl1 – Trac Ngon

1 Answers

0
votes

Try below query.

select name, (select TOP(1) job from tbl2 where tbl1.type = tbl2.type) from tbl1

Hope it help