0

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

Improve this question
4
  • Please clarify if table B is used to store the length of the First/Last names Commented Oct 10, 2014 at 17:06
  • Yes, B is used to store the length Commented Oct 10, 2014 at 18:50
  • well in that case my answer is the simplest query to get the desired result Commented Oct 10, 2014 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. Commented Oct 10, 2014 at 18:56

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Thanks, inner join seems to be the best choise.
It return syntax error, should I define b1 or b2 first please? Thanks again
It seems doesn't like the second inner join, should add "," between them please? thanks.
@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.
I see what is going on now. Access use a different format of double inner join, please check my update. Thanks again.
|
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
Improve this answer

4 Comments

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
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
It could be simply poor example data, but we need clarification from OP to be sure.
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.