SQL/MySQL query help.

thecookie

Hi, I need help with construction a somewhat complex mysql query 🙂 I don't even know if it can be done with just 1.

I have a table with rows heh, they are linked to each others with IDs like a chain. One row is linked to another then that row to another. I want to select them in that order.

One of the links has no link over them, so I have figured I first have to select that one, then try to select the rest in the right order.

Can this be done? Or do I have to solve it with multiple queries?

Thanks for any help!

nnichols

You can force the order in which mysql makes it's joins by using a STRAIGHT JOIN, but it sounds like you will need to use LEFT JOINs.

If you post again with more detail and an example of the data and what you want returned I might be able to give more help. Table structures would also be useful.

thecookie

+----+------------+------------------- +
| id | CID | PID |
+----+------------+------------+-------
| 1 | 1343247693 | NULL |
| 2 | 1343264672 | 1343247693 |
| 3 | 1343268352 | 1343264672 |
+----+------------+------------+------ +

This is how it looks. CID is the main ID, PID is the "connection" id.. in this table.. Because id 1 is the top of the chain it dosn't have a connection.. It's the fist one.. then id 2 is connected to id 1 trough the PID field..

In this table i would like to have a select that selects it in this order, 1,2,3.

Thanks!

nnichols

Please give examples for data from each of the tables you want to join and an example of what should be returned from the query.

thecookie

I have two tables. One named Members the other named Chain. Members got the main data stored. Chain has the structure of the members stored. What I want from Members is a field called name. The order I want to select is how the Chain table is linked. So when I loop trough the result it will begin at the top of the chain, then end at the end of it 😉

nnichols

Sorry, I can't understand your explanation. For a better attempt post some sample date like -

table Members

+----+------------+------------+
| id |     CID    |    PID     |
+----+------------+------------+
|  1 | 1343247693 | NULL       |
|  2 | 1343264672 | 1343247693 |
|  3 | 1343268352 | 1343264672 |
+----+------------+------------+

table Chain

+----+------------+------------+
| id |     CID    |    PID     |
+----+------------+------------+
|  1 | 1343247693 | NULL       |
|  2 | 1343264672 | 1343247693 |
|  3 | 1343268352 | 1343264672 |
+----+------------+------------+

example of data to be returned

+----+------------+------------+
| id |     CID    |    PID     |
+----+------------+------------+
|  1 | 1343247693 | NULL       |
|  2 | 1343264672 | 1343247693 |
|  3 | 1343268352 | 1343264672 |
+----+------------+------------+

obviously changed so that the col_names reflect those of your tables and likewise for the data.

nnichols

is the chain always a set length? or does it have a set maximum length?

thecookie

It dosn't have a static lenght... And the order of the chain can be changed..

table Members

+----+------------+------------+
| id |     CID    |    name    |
+----+------------+------------+
|  1 | 1343247693 | name1      |
|  2 | 1343264672 | name2      |
|  3 | 1343268352 | name3      |
+----+------------+------------+

table Chain

+----+------------+------------+
| id |     CID    |    PID     |
+----+------------+------------+
|  1 | 1343247693 | NULL       |
|  2 | 1343268352 | 1343264672 |
|  3 | 1343264672 | 1343247693 |
+----+------------+------------+

example of data to be returned

+----+------------+------------+
| id |     CID    |    name    |
+----+------------+------------+
|  1 | 1343247693 | name1      |
|  2 | 1343264672 | name2      |
|  3 | 1343268352 | name3      |
+----+------------+------------+

That clears stuff up? 🙂 I altered the 'Chain' table just to illustrade that the chain table isn't always sorted the way I want it returned..

nnichols

I can't come up with an efficient way of doing this with your existing structure. Is the relationship between parent and child "1 <=> 1" or "many <=> many" or "1 <=> many"?

thecookie

There are no relations beteen them, the Chain table just describes the order I want to select the data from the Member table. Can you come up with a better idea to get them in the order I want?

nnichols

Is this correct -
each user's id occurs a maximum of twice in the chain table - once as CID and again as PID? Or can it be entered more?

thecookie

That is correct.

nnichols

In that case you can do away with your "Chain" table completely by using just a reference to the child or parent in the Member table.

Do you know what the max chain length is likely to be? Will it be greater than 10?

If so the most efficient way to handle this will be to do it in php with multiple queries not in sql.

so you run the first query to get the top level parent (or bottom level child) and based on the result of that you run another query if there is another child in the chain other wise you stop, and so on. You can do this with a simple loop.

I can't come up with an easy way to achieve this n-level chain with a single select statement.

If you really want to try doing it this way you should post again with a subject something like - "N-level self reference" and post the data structure above and see if someone comes up with a better solution.

Sorry I can't be of more help 🙁