CODEWITHSUNDEEP
sqljoin
Christoph_Austria_sql beginner
Christoph_Austria_sql beginner

Reputation: 5

Join 2 tables using the combination of 2 columns

I am trying to join two tables. Unfortunately there is no single column to join them. Only the combination of 2 columns (in each of the tables) creates a unique identifier that enables me to make an inner join. How do I do that?

edit: someone suggested making a join using AND. Unfortunately this does not seem to work.

Here is an example of the tablse

Table 1

Order no | Operation no | . ...

FWA1 | 10

FWA2 | 20

FWA3 | 10

Table 2

Order no | Operation no | Description

FWA1 | 10 | drilling

FWA2 | 20 | grinding

FWA3 | 10 | buffing

(please notice that operation no 10 can have a different description in different orders.)

Upvotes: 0

Views: 4991

Answers (6)

Mital Solanki
Mital Solanki

Reputation: 171

Use This Query :

SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

Or

SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

Upvotes: 0

Kaf
Kaf

Reputation: 33829

I think this will do the job

select t1.orderNo, t1.operationNo, t2.description
from Table1 t1 inner join Table2 t2 
         on t1.orderNo = t2.orderNo and 
            t1.operationNo = t2.OperationNo

Upvotes: 1

JanR
JanR

Reputation: 6132

rIt depends on the relation between the two tables if it's a 1-to-many relation you can join them using two combinations:

select *
from table1 a
join table2 b on a.column1 = b.column1 AND a.column2 = b.column2

if the relationship between the tables is many-to-many you will need a linking table which would contain a unique id, value1 and value 2 where each row marks a combination of value1 and value2

Hope that helps..

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Just use both columns in JOIN:

SELECT * FROM table1 t1
INNER JOIN table2 t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2

Upvotes: 0

Daniel W.
Daniel W.

Reputation: 32300

SELECT * FROM table1 A
    JOIN table2 B ON
    A.first = B.first AND A.second = B.second

Upvotes: 0

Sklivvz
Sklivvz

Reputation: 31133

Try the following

SELECT *
FROM t1, t2
WHERE t1.f1 + t1.f2 = t2.f3

On the other hand if you have 2 columns on which you want to join then DanFromGermany's answer is appropriate.

Upvotes: 0

Related Questions