I will buy a book of the helpers choice from Amazon.com (within reason up to $50ish) for help solving my problem:
I need to do something kinda odd that I'm hoping to get help with...except for any suggestions of changing my base table structure. (Although if there was someway to make additional columns/tables that would help) This might seem weird but I need to try and make it work as it is. I have a table called pwdata that looks kinda like the following: id field value thread 0 3 perkins 1 1 2 mark 1 2 6 colorado 1 3 3 rogers 2 4 2 bill 2 5 6 texas 2 6 3 adams 3 7 2 nancy 3 8 7 summer 4 9 8 rain 4 10 3 stemm 5 11 2 ethan 5 12 6 kansas 5 13 6 utah 6 14 3 murray 6 15 2 bill 6 15 9 sales 6 I need a list of threads where field is equal to one or more of three values (2 or 3 or 6 in this example) and be able to sort by any of these fields. If I use something like this: SELECT c0.thread, c1.value, c2.value FROM pwdata AS c0 LEFT JOIN pwdata AS c1 ON c0.thread = c1.thread LEFT JOIN pwdata AS c2 ON c0.thread = c2.thread WHERE c1.field IN (2,3,6) AND c2.field=3 GROUP BY c0.thread ORDER BY c2.value I get a three column return with a row for each unique thread sorted by whatever value I assign to c2.field. This works great except where I try to sort by a field that does not exist in all of the threads. I need it to show me the total number of rows (five: threads 1,2, 3,5,6 for the example above) regardless of how I sort. For example, by setting c2.field above to 3 it shows five columns and sorts them by the value of field 3. If I set it to 2 it shows five columns and sorts them by the value of field 2. If, however, I set c2.field to 6 it only shows 4 columns and sorts them. I can see why, but I am desperately seeking a query with the structure above that I can search by a set of three and sort by any of them. It is relatively straight forward for me to pull the thread info I need, but there does not appear to be much in the way of sorting options. I'm hoping there might be some way to work with the same table in a different way with Left Joins so to preserve the number of returned rows by creating 'null' values for the missing values in each thread. I'm not sure if this made any sense... Any ideas? Quinn --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
