[SOLVED] ORDERING BEFORE GROUPING OR SOMETHING LIKE THAT

[aviddv1]

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

 

 

[xtopolis]

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

[fenway]

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.

[aviddv1]

Thanks for the help guys.