CODEWITHSUNDEEP
javasqldatabasejoin
BelieveToLive
BelieveToLive

Reputation: 291

Joining multiple tables which satisfies multiple columns in both the tables

I want to get the data from two tables which has one similar column. 

Table A

x   y   z  a  b  

1   48 36  d5 8d  
2   78  36 2t 15
3   99  54  s5  14
4   48  73  58  11



Table B

x   y   z  a  b  

1   48 95  d9 69  
2   98  36 3c vv
3   93  39  df  1bb
4   48  73  58  11

I want to get the y,z,b values from the table when column y=48,z=73,a=58,b=11. I used the join query like this but its not working.

Select x,y,z,a,b from A join B in A.y=B.y=48 and A.z=B.z=73 and A.a=B.a=58 and A.b=B.b=11;

Can you give me the right solution

Upvotes: 0

Views: 46

Answers (2)

user330315
user330315

Reputation:

Joins are defined through the ON keyword not using IN. You are also mixing the join condition and the filter condition. a=b=x doesn't work in SQL.

You will also need to fully qualify the columns in your select list, otherwise you'll get an error that the column name is ambiguous.

Select a.x, a.y, a.z, a.a, a.b 
from A 
  join B on A.y=B.y 
        and A.z=B.z 
        and A.a=B.a 
        and A.b=B.b
where a.y = 48
  and a.z = 73
  and a.a = 58
  and a.b = 11

Your comment "which has one similar column" seems to indicate that the join should in fact only be done through the column x not based on all columns you want to filter on:

So you might actually want:

Select a.x, a.y, a.z, a.a, a.b 
from A 
  join B on a.x = b.x -- only use `x` for the join condition
where a.y = 48
  and a.z = 73
  and a.a = 58
  and a.b = 11

Upvotes: 2

Shantanu
Shantanu

Reputation: 186

try below

SELECT
    A.X,
    B.Y,
    A.Z,
    A.A,
    A.B
FROM
    A,
    B
WHERE A.Y=B.Y AND A.Y=48
AND A.Z=B.Z AND A.Z=73
AND A.A=B.A AND A.A=58
AND A.B=B.B AND A.B=11;

Upvotes: -1

Related Questions