SELECT in N:N relationship without using INNER JOIN

Asked

Viewed 182 times

0

I need an SQL equivalent to a SELECT using INNER JOIN as I am using Sphinxsearch and read that it does not support INNER JOIN. I know you can do it using Sub-Query but I can’t remember how you do it.

This is the bank: Google Drive

This is the SELECT:

SELECT projeto.id, orientador.idOrientador 
    FROM projeto INNER JOIN projeto_has_orientador ON (projeto.id = projeto_has_orientador.Projeto_id ) 
        INNER JOIN orientador ON (projeto_has_orientador.Orientador_idOrientador = orientador.idOrientador);

This was an unsuccessful attempt to create a SELECT with Sub-Query:

SELECT projeto.id, orientador.idOrientador 
FROM projeto, orientador 
    WHERE projeto.id 
    IN (SELECT Projeto_id FROM projeto_has_orientador WHERE Orientador_idOrientador
    IN (SELECT idOrientador FROM orientador);

Grateful.

mysql sql

  • has to know what result you are waiting with it... if I understand... => http://sqlfiddle.com/#! 9/1996d71/1

  • 1

    Understanding that by it does not support INNER JOIN you mean just the operator’s interpretation INNER JOIN try: SELECT projeto.id, orientador.idOrientador 
 FROM projeto, projeto_has_orientador, orientador
 WHERE projeto.id = projeto_has_orientador.Projeto_id
 AND projeto_has_orientador.Orientador_idOrientador = orientador.idOrientador;

2 answers

1

To do JOIN would need to have a foreign key relationship between the tables. For example, it would insert in which project the advisor this:

ALTER TABLE orientador ADD COLUMN projeto_id INTEGER;

ALTER TALBE orientador ADD CONSTRAINT fk_projeto_orientador 
FOREIGN KEY (projeto_id) REFERENCES projeto(id);

Ai JOIN SQL could be used:

SELECT projeto.id, orientador.idOrientador
FROM projeto
INNER JOIN orientador ON orientador.projeto_id = projeto.id;

That was just an example. If your advisor may be contained in more than one project it might be interesting to create a foreign project key for advisor and not the other way around as I did above.

Behold:

Foreign key: https://www.w3schools.com/sql/sql_foreignkey.asp

Entity and relationship: https://www.devmedia.com.br/modelo-entidade-relacionamento-mer-e-diagrama-entidade-relacionamento-der/14332

  • 1

    The SQL standard does not require specifying a foreign key to use a junction, any type of JOIN. What is required is that the fields used in the junction are comparable.

  • I understand the obligation not to. I suggested the foreign key because I felt the need to have one. The relationship with use is better understood. Even more so when it comes to project and project oriented abstraction. Better standardization, and unnecessary code reduction.

  • Anyway, I wanted to help, and I think any solution that brings the desired effect is enough

  • In the SQL I sent contained the "Foreign Keys", because it is a relation from N to N, in this case, there is a table in the middle, which is the Project_has_advisor, which joins the projects with the advisors. The information I was wanting, is how I do to have a SELECT that plays the same role as an INNER JOIN, because Sphinxsearch doesn’t really support.

  • cool guy, I confess I didn’t pay attention. I tried to help in the midst of the rush :D

0


It turned out that I didn’t even need the sub-query, because the following happens in Sphinx:

I have a configuration file in which I put the SELECT in which it will do in the database to index the records, in this file I can use the INNER JOIN, because it follows the SQL language of the database in which it will do the index.

After the index is done, it has a language of its own to search on top of the indexed records, which does not support INNER JOIN.

I only asked him to create an index on top of a SELECT with INNER JOIN, which then he would index the results in a single table in Sphinx.

Browser other questions tagged mysql sql

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