Access SQL selecting multiple columns from linked tables

Question

I have two tables. Table A:

First_name Last_name score 
   Joo      jojo      100
   Jake      Tim       30

Table B:

Name  length
Joo     3
Jake    4
Jojo    4
Tim     3

The PK for A is join of FIrst and last name, for B pk is name. If I want to print something like this:

First_name Last_name score  First_name_length   Last_name_length

How should I fix from the following code:

Select A.*, B.length
From A,B
Where A.First_name=B.Name or A.Last_name=B.Name;

Thanks.

Update: I solved the problem with a little change from the answer below by @paqogomez. In access, with double inner join, one should use ( ), please check this post. Thank you all for your kindly help.

double inner join in access db

Please clarify if table B is used to store the length of the First/Last names — Ram, Oct 10 '14 at 17:06
well in that case my answer is the simplest query to get the desired result — Ram, Oct 10 '14 at 18:52
The difficult part in my opinion is I don't know how to deal with Table B, because A.last_name and A.First_name both relate to it. — henduo qian, Oct 10 '14 at 18:56

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

Joining to your name table twice will accomplish this:

select
  b1.name as First_name,
  b2.name as Last_name,
  a.score as score,
  b1.length as First_name_length,
  b2.length as Last_name_length
from
  (a
  inner join b as b1 on a.First_name = b1.name)
  inner join b as b2 on a.Last_name = b2.name

The problem you will run into however is if 2 people have the same first or last name. It would be better to clean up your data and give each user an unique value.

EDIT:

I assume that you want to use the value from your table B, if you are really just getting the string length of the name, then please use @Srikanth's answer.

While OP is using Access, I made this fiddle using sql server that shows it working.

Parens above added for access's double inner join

edited May 23 '17 at 10:33

Community

1
1

answered Oct 10 '14 at 16:48

crthompson

15,653
6
58
80

Thanks, inner join seems to be the best choise. – henduo qian Oct 10 '14 at 17:07
It return syntax error, should I define b1 or b2 first please? Thanks again – henduo qian Oct 10 '14 at 18:52
It seems doesn't like the second inner join, should add "," between them please? thanks. – henduo qian Oct 10 '14 at 19:19
@henduoqian No, you are using older sql 89 syntax in your OP. With `inner join` (sql 92 syntax) there is no comma between the tables. You can see it working in the fiddle that I posted. – crthompson Oct 10 '14 at 19:22
@henduoqian, if you're getting an error, please show me the exact message. – crthompson Oct 10 '14 at 19:24
Thanks for the continue reply. It shows"Syntax error(missing operator) in query expression'GAMES.[Home team]=TEAMS1.Name inner join TEAMS as TEAMS2 on GAMES.[Visiting team]=TEAMS2.Nam'. PS: I use a different table names here. – henduo qian Oct 10 '14 at 19:32
@henduoqian Using [this fiddle](http://sqlfiddle.com/#!3/8aeb5) post your whole query. – crthompson Oct 10 '14 at 19:39
@henduoqian, this is sounding like it is probably another question. If I can see the syntax of your changes, i'm sure I could fix them. If you change your tables to their real names, i'll create a proper query. However, you might want to just post another question. – crthompson Oct 10 '14 at 19:49
1

I see what is going on now. Access use a different format of double inner join, please check my update. Thanks again. – henduo qian Oct 10 '14 at 19:49

score 1 · Answer 2 · answered Oct 10 '14 at 16:50

1

The following code with Len is sufficient for the desired output

Select First_name, 
       Last_name, 
       score,  
       len(First_name) AS First_name_length, 
       len(Last_name) AS Last_name_length
From A

answered Oct 10 '14 at 16:50

Ram

3,092
10
40
56

lol.. i didnt even consider that he was just looking for the length of the string name. This would be the answer if that is the case. +1 – crthompson Oct 10 '14 at 16:55
Yeah, initially I thought of using two joins similar to yours but with another glance at the data in table B I felt this should do the job – Ram Oct 10 '14 at 16:59
It could be simply poor example data, but we need clarification from OP to be sure. – crthompson Oct 10 '14 at 17:01
Thanks for your answer. Really open eyes for me. But I am not looking for string length only, just want to know how to select column from linked tables. But your answer is really a short cut and wise idea. – henduo qian Oct 10 '14 at 17:06

Access SQL selecting multiple columns from linked tables

2 Answers2