Is this possible in single select

Databases
1

Hi,

I have a table with 2 INT cols

jobNo | relatedNo

it stores the id of a job file and the id of a related job file which is created from the job file.

each entry creates 2 rows:
job file id | related file id
related file id | job file id

since it is possible to create related job files from any job file, including related job files (they are all job files) you can get a string of related files eg
jobNo | relatedNo
1 | 5
5 | 1
5 | 18
18 | 5
5 | 27
27 | 5
27 | 32
32 | 27
18 | 55
55 | 18

Is it possible to select all the files that are connected in one select, as in the eg jobNo 1 has jobNo 5 related, 5 has 18 related, 5 also has 27 related and so on. Duplicates are fine as I can remove them later with php.

Any pointers to tutorials much appreciated or clues as to how to construct the mysql select.

Thanks

2

Why do you have the double entries? 1-5 and 5-1, 5-18 and 18-5?

3

Hi,

the php was already set up to create these, I think it is to do with the job file pairs and how they are used in other parts of the system, I think I can probably change this and remove the second entry once I can select all linked files and read further into what the second entry is used for exactly.

I am building an additional report, part of this is required to display a list of all related files for each job file shown in the report.

4

simple answer: for optimum query performance

it is the difference between this –

  FROM friend AS f1
INNER
  JOIN friend AS f2
    ON f2.friend_id = f1.id

and this –

  FROM friend AS f1
INNER
  JOIN friend AS f2
    ON f2.friend_id = f1.id
    OR f2.id = f1.friend_id

you already know which query is going to be faster, don’t you :cool:

the second query is often done as a UNION because in a single SELECT, mysql can’t optimize it at all

5

Ok.
But let’s return to the OP’s question: is it possible to get all related jobs in 1 query?

If you know the max depth, yes. Right?
But doesn’t that query get more complicated with all those double entries?

6

right

but it doesn’t get more complicated with the double entries, it stays simple

7

can you give me a pointer to the use of the max depth please.

I am beginning to think this may need to be done in multiple queries. I was hoping to manage in one but I’m still pretty new to mysql so maybe I got it wrong and it needs to be multiple.

Thanks for your help here, I’m trying to learn fast!

8

it’s like a hierarchy of relatedness, or more like a network

a file related to a second file is two levels

that second file related to another file is three levels

for each extension of the relatedness, you need another LEFT OUTER JOIN but you’re alwys joining the table to itself

up to about 15 self-joins is practical and efficient

more than that, and you have issues even displaying the results, never mind how you retrieve them…