CODEWITHSUNDEEP
mysqlsqlselect
Rolch2015
Rolch2015

Reputation: 1541

Complicated select statement with SQL (mysql)

i am writing an application and search for a specific select statement. The situation in my MYSQL database is the following:

I realised this situation with the following tables:

What I want now is the following: I want that the user inputs a subunit.id and the gets a list with all entry.ids, which contain the input subunit.id, as well as all other subunit.ids, which are related to those found entry.ids. The result should looke like:

(input = subunit 5)

entry.id | subunit.id

entry 1 | subunit 5

entry 1 | subunit 26

entry 2 | subunit 2

entry 2 | subunit 5

...

To only get the entry.ids, which are related to the input subunit.id, was easy. But I falied to get eveything together into one SQL statement. I solved it in my application, by creating two different statements (whereby the second statement is executed for each found entry.id). Is there an elegant solution, to put this all into one sql statement?

Thank you very much!

UPDATE 1

To avoid misunderstandings like in the comment to this question: There is actually a dataset. But in order to make the question as theoretical, general and comprehensible as possible, i reduced it only to "ids".

Upvotes: 0

Views: 92

Answers (1)

Fox
Fox

Reputation: 2368

As first shot, I would try something like this. It might not work right away (I have not tested, sorry), but it might give you an idea of a possible solution. I would not call it elegant, and I would be very careful, especially if the tables and/or expected outputs are large. It might not be optimal and might work better if split into two statements.

SELECT e.id AS entryid, us2.subunit_id AS subunitid,
    FROM unit_subunits AS us1 
        JOIN unit AS u1 ON (us1.unit_id = u1.id) 
        JOIN entry AS e ON (u1.entry_id = e.id)
        JOIN unit AS u2 ON (e.id = u2.entry_id)
        JOIN unit_subunits AS us2 ON (u2.id = us1.unit_id)
    WHERE us1.subunit_id = 5;

Upvotes: 1

Related Questions