I am not an SQL pro, and last time did queries 10 years ago. Now I need to make one, and just can't seem to finalize.
Very hard to explain and define the problem in right words, thus I can't find solution in Google myself, but hopefully for you, experts, it will be an easy one.
There are 3 tables, and tons of files in Windows filesystem with different random names (like 1ox45z0.doc)
From the database I need to get metadata about records, which are related to some of the files mentioned above.
So, how the tables look like?
1. Main one - with almost all the data I need in output
2. Relation one - with related item IDs from the main table.
3. Path table - with just record system IDs and their appropriate filenames in Windows filesystem.
The 1. table contains records, which are directly connected to 3. table records, so it would be no problem to get out filenames (from 3.) and corresponding data (from 1.) The problem is - I need data (from 1.) of other records, which are related to previously mentioned data through the relation table (2.)
Basically that means, I need to get A (data from 1.), which are related to B (another data from 1.) through table 2., and additionally one column - path from 3., which refers to B, not to A. Is it even possible? Because A also has their own path in 3.
Currently my structure looks like this:
First select takes data I need; where and second select help me to extract particular related records I need.
Everything works well, except the marked out part, because query, of course, returns 3.path from A data, not B data
If you could only give a direction where to dig, it would help a lot.