Complicated select statement with SQL (mysql)

Question

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

Each entry can have several units. One unit is related to exactly one entry.
Each unit can have several subunits. One subunit can be related to several units.

I realised this situation with the following tables:

entry: id (int)
unit: id (int), entry_id (int)
unit_subunits: unit_id (int), subunit_id (int)
subunit: id (int)

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".

A result set with no data set is as daft as it is meaningless. — Strawberry
– Strawberry, Commented Mar 19, 2015 at 21:34
Dear @Strawberry, what do you exactly mean? There is of course a data set and actually the entry.id is a unique varchar as well as the subunit.id. But to make the question as theoretical and simple (and therefore as comprehensible as possible) i just reduced it to "id". Or do you mean, I should give you the data? If it is the first case, I would be glad to hear your constructive criticism. — Rolch2015
– Rolch2015, Commented Mar 19, 2015 at 21:45

Fox · Accepted Answer · 2015-03-19 21:28:03Z

1

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;

answered Mar 19, 2015 at 21:28

Fox

2,36819 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rolch2015 Over a year ago

Thank you very much @Fox! Your answer right away solved the problem and more important, it taught me a lot about how to use JOINs in such a case. Cheers!

Collectives™ on Stack Overflow

Complicated select statement with SQL (mysql)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related