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

Asked

Viewed 389 times

-1

I have a table that has the following structure:

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

The column Master_key can have two values: 'NY' OR 'MS', I would like to compare the lines where Master_key is equal to 'NY' with those who are 'MS', the source, paper and market columns are keys.

I tried this way, but it didn’t 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:inserir a descrição da imagem aqui

I want you to return this way:inserir a descrição da imagem aqui

mysql sql query inner-join

  • I don’t understand what you want but it sure ain’t with INNER JOIN, it serves to make queries joining different tables. Could give an expected output example?

  • I added an example to the question

  • The relationship between the two rows is if all columns are equal however Master_key different?

  • @Guillhermecostamilam Master_key can only have two results, so it has a Where at the end of the query

1 answer

2


From what I understand, all it takes is one 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');

Browser other questions tagged mysql sql query inner-join

You are not signed in. Login or sign up in order to post.