CODEWITHSUNDEEP
mysqljoincomparison
anthony
anthony

Reputation: 69

Simple Inner Join for comparing two values in table

I'm currently stuck at figuring out what could be considered a rather basic and common inner join problem:

I have two tables orders and locations:

orders

|----------------------|-------------------|-------------------|
| foreign_from_id      | foreign_to_id     |    same_triade    |
|----------------------|-------------------|-------------------|
|          US          |        MX         |                   |
|          US          |        DE         |                   |
|----------------------|-------------------|-------------------|

locations

|----------------------|-------------------|
|       foreign_id     |       triade      |
|----------------------|-------------------|
|          US          |         AME       |
|          MX          |         AME       |
|          DE          |         EU        |
|----------------------|-------------------|

My code is supposed to check if the foreign_from_id and the foreign_to_id is in the same triade and return a 1 or 0 in same_triade. Helping me figuring out this one would help me solve a lot of my SQL problems.

Thanks in advance!

Upvotes: 1

Views: 1200

Answers (2)

Alberto Moro
Alberto Moro

Reputation: 1013

Starting from @PeterHe's query I wrote this:

UPDATE orders
INNER JOIN locations fl ON orders.foreign_from_id=fl.foreign_id 
INNER JOIN locations tl ON orders.foreign_to_id=tl.foreign_id 
SET orders.same_triade = (CASE WHEN fl.triade=tl.triade THEN 1 ELSE 0 END)

DEMO

Upvotes: 1

PeterHe
PeterHe

Reputation: 2766

This should work:

SELECT o.foreign_from_id, o.foreign_to_id, CASE WHEN fl.triade=ol.triade THEN 1 ELSE 0 END AS same_triade
FROM orders o
INNER JOIN locations fl
ON o.foreign_from_id=fl.foreign_id 
INNER JOIN locations tl
ON o.foreign_to_id=tl.foreign_id;

Upvotes: 1

Related Questions