aviddv1 Posted January 5, 2009 Share Posted January 5, 2009 Hey there, I've got three tables. One table, t1, has a list of attributes like work, home, image and swf. t1: +----+----------+ | aid | attribute | +----+----------+ | 1 | work | | 2 | home | | 3 | image | | 4 | swf | +----+----------+ The second table, t2, has a list of ids relating t1 to t3. nid 3 appears twice because it is related to both the 'work' attribute as well as the 'swf' attribute. t2: +----+----+ | nid | aid | +----+----+ | 1 | 3 | | 2 | 4 | | 3 | 4 | | 3 | 1 | | 4 | 3 | +----+----+ The third table, t3, has a list of my articles and sort orders. There are four articles. t3: +----+----------+---------------+ | nid | name | sort_order | +----+----------+---------------+ | 1 | article 1 | 1 | | 2 | article 2 | 0 | | 3 | article 3 | 0 | | 4 | article 4 | 2 | +----+----------+---------------+ I want any records associated with the two location attributes (home or work) to come first in the results followed by the records associated with image or swf ordered by their sort order. I also want to show the attributes as location and type. records that aren't associated with a location should just show null. If I run a query based on t2 I will have five records where one record is a duplicate. I'm looking to eliminate the duplicate record. desired results: +----+----------+------------+-------------+ | nid | name | location | type | +----+----------+------------+-------------+ | 3 | article 3 | work | swf | ---> comes first since it's a location attribute (home or work) | 2 | article 2 | null | swf | ---> sorted by sort order in t3 since there is no location association | 1 | article 1 | null | image | only image or swf for nid 1,2,4. | 4 | article 4 | null | image | +----+----------+------------+-------------+ Hope this wasn't too confusing. I've tried grouping. I've tried distinct. I'm a moron. Thanks, Howie Quote Link to comment https://forums.phpfreaks.com/topic/139567-solved-ordering-before-grouping-or-something-like-that/ Share on other sites More sharing options...
xtopolis Posted January 6, 2009 Share Posted January 6, 2009 I don't think this will work seeing as your desired results try to pull info from the same column. Why not just create a separate location field... I could possibly see it working then, location being separate from type. Or are you missing a table listed here? Also post your query you're using as well. Also to order in a certain order: ORDER BY FIELD(columnName,'ColumnValue','ColumnValue'); //unlisted ones are ordered regularly after that Quote Link to comment https://forums.phpfreaks.com/topic/139567-solved-ordering-before-grouping-or-something-like-that/#findComment-730561 Share on other sites More sharing options...
fenway Posted January 6, 2009 Share Posted January 6, 2009 Also to order in a certain order: ORDER BY FIELD(columnName,'ColumnValue','ColumnValue'); //unlisted ones are ordered regularly after that Note that if the columnName does NOT match of the of the listed values, it will sort FIRST... so you may want to "reverse" it. Quote Link to comment https://forums.phpfreaks.com/topic/139567-solved-ordering-before-grouping-or-something-like-that/#findComment-730609 Share on other sites More sharing options...
aviddv1 Posted January 6, 2009 Author Share Posted January 6, 2009 Thanks for the help guys. Quote Link to comment https://forums.phpfreaks.com/topic/139567-solved-ordering-before-grouping-or-something-like-that/#findComment-730619 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.