Complex MySQL Query?

Anon

Hey everyone,

Hoboy... Time for another "Let's make a long line of back and forth posts" until someone understands what the heck is going on. Hopefully my first post will explain some of this. And hopefully someone will hint me towards what I need to look at and bother with reading all of this.

Well, here's the thing. I got 2 tables in a database, both of which are useless without the relation to one-another. Table one contains a long list of numbers, and to each, the meaning, and which "list" it belongs to. The application I've built has about 8 lists in which these entries appear. The 2nd table is basically the storage table which also has a unique number for each entry as well as one column per these "lists" (8 as I said before) and in each of these columns a number corresponding to the unique number in table one is stored. So as you see, the 2nd table relies on the 1st to know what the hell the numbers mean.

What I'm trying to do is to build some stats using PHP. And to do this, I've broken up the whole magilla into 3 seperations. Basically 3 lists which in correspondance with one another is checked to make sure each combination has been looked at. Sort of like this:

111
112
113
121
131
132 and so forth...

What happens is, my program looks at the 1st table, and sorts by 3 lists. Let's just call em a, b and c. It looks how many entries are in a, b and c. a has 2, b has 3 and c has 7. To make it even harder, even these entries have relations to one another. Only one entry in c is displayed when b is 1. 5 entries from c are displayed when b is 2. And 1 is once again displayed when b is 3. Never the same entries though. So it looks something like this:

111
121
122
123
124
125
131
211

oh god I think noone is gonna get this. Nevertheless - I'm looking for a way to get MySQL to understand this and make queries while considering these conditions, rather than building 3 loops in one another and making those circle the results because it ends up straining the server more than is necessary. Basically making only 1 query for each result and getting 1 answer <😛

Any ideas?

Gazoo...

Anon

Sorry, I can't comprehend what you are trying to do.

Table 1 contains three columns (a "number", a "list id", and a "meaning")? You say the number is unique, but you example shows duplicates. Is the "number" a unique id for rows in that table? Or can the same number be appear with different listids? I'll guess the combination of number and listid is unique:

Table 1
num Listid meaning
1 a meaning of a1
2 a meaning of a2
1 b meaning of b1
1 c meaning of c1
etc.

Table 2 contains 9 columns? A unique id and eight columns that point back to Table1?

Table 2
num L1 L2 L3 ...
1 1 1 1 ...

So this row in table two points to the first, third, and fourth rows in Table 1?

Where did the three lists you talk about come from? Are you ignoring five of the columns in Table 2?

And what are you trying to find in all of this?

[deleted]

hehe just getting your question clear can be a problem on its own 🙂

I think it would help if you gave some sample data from your tables, and a printout of your table structure would help (like the output from DESCRIBE, and, SELECT * FROM table LIMIT 10) to helpus visualise what theF you're talking about.

Anon

Switch to Postgres and write a stored procedure in 5 minutes that will do exactly what you want, and will be precompiled and optimised to boot.

(Sorry for the totally unhelpful answer 😉 )

Chris

Anon

Hey yall,

And thanks first off to tomhath, vincent and Chris for simply trying 🙂. I can see it's simply no use doing this without explaining the exact problem. Ok - Try and bear with me on this one... Think of the application as 11 lists with entries in them, some of which dynamically change depending on the choices in the previous lists...

The first table is reponsible for displaying these lists in the application, and in turn also keeping in check which entries belong where and in what order.

The first table consists of 5 columns. The unique ID (Number), and ID for the order in which the entry is listed (Number), the actual displayed name of the entry (text), the list the entry belongs to (text), and finally the referrer ID (Number) - which helps determine which entry called this entry... The referrer ID would match a unique id.

The second table consists of a column for the unique ID for each entry, a column for each existing list (11 in total) and a few other uninteresting columns. This table is used for storing the entered results.

What I'm building now - is the statistic for the entered data - and the first thing I need to do, is to split up the search criteria using 3 of the lists in the application. One of which changes dynamically. In any case I need to do a search for certain fields and each time, the search must be done once with every possible combination of those 3 lists...

List 1 has 3 entries
List 2 has 3 entries and is also the referrer for List 3 which changes dynamically depending on the selection in List 2.

Once again I'll be showing this example based on the ID which decides in which order they are listed - NOT the unique Id.

1-1-1
1-2-1
1-2-2
1-2-3
1-2-4
1-2-5
1-3-1
2-1-1
2-2-1
2-2-2
2-2-3
2-2-4
2-2-5
2-3-1
3-1-1
3-2-1
3-2-2
3-2-3
3-2-4
3-2-5
3-3-1

That's the number of times I need to look for a number of maybe 50 fields in the 2nd table. And doing this in a simple search each time is a waste of CPU. That is why I'm wondering if it's possible to formulate a query to let MySQL circle through these criterias for the fields itself, and not need me to build 3 entwined loops for each query.

MySQL won't need to read from table 1 while searching for these criterias in table 2 - cause I can let PHP handle the var transfers...

Maybe this clears it up a little?

Gazoo... 😃

Anon

I'm going to take a wild guess and suggest this might be what you want. You need to join a three rows from Table1 with one row from Table2?

The first table (Table1)
uniqueID,
sortorder,
listid

The second table (Table2)
uniqueID,
column1, column2, column3, ...

select a1.sortorder, b1.sortorder, c1.sortorder
from table1 a1, table1 b1, table1 c1, table2 t2
where a1.listid = $mylist
and a1.uniqueID = t2.column1
and b1.listid = $mylist
and b1.uniqueID = t2.column2
and c1.listid = $mylist
and c1.uniqueID = t2.column3
order by a1.sortorder, b1.sortorder, c1.sortorder