How to make an INNER JOIN in a single table with 3 keys?

Question

How to make an INNER JOIN in a single table with 3 keys?

Navigation

#1 by (2 votes)

-1

I have a table that has the following structure:

origem|papel|campo018|campo040|mercado|Master_key|

The Master_key column can have two values: 'NY' OR 'MS' , I'd like to compare the rows in which Master_key is equal to 'NY' with those that are 'MS' , the source, role, and market columns are keys.

I tried this way, but it did not work:

SELECT ny.*, ms.* FROM z_quotes ny
INNER JOIN z_quotes ms ON ny.origem = ms.origem
INNER JOIN z_quotes ms ON ny.papel = ms.papel
INNER JOIN z_quotes ms ON ny.mercado = ms.mercado
WHERE (ny.Master_key = 'NY') AND (ms.Master_key = 'MS');

For example, if I have the table below:

I want it to return this way:

mysql sql query inner-join

asked by anonymous 19.06.2018 / 20:49

1 answer

How to make two loops for the same code only go varying the columns of the courses and the columns of the questions? How do I list all the properties of an object?

score 2 · Accepted Answer

As I understand it, just a JOIN :

SELECT ny.*, ms.* FROM z_quotes ny
INNER JOIN z_quotes ms ON ny.origem = ms.origem
  AND ny.papel = ms.papel
  AND ny.mercado = ms.mercado
WHERE (ny.Master_key = 'NY') AND (ms.Master_key = 'MS');