order by condition problem

[oliveralden]

Hi there.  I've got a rather complicated select statement with multiple joins.  In the final display, I need to order by SOME of the rows in one of the columns.  I'm not sure how to begin tackling this problem.  Could I make an array out of one column & then assign a variable to every 3rd key & sort by the variable?  Can I write a select statement within a select statement?  Should I populate a new table of some kind?  I've tried using group by without results.

Here's the select statement as it stands:
$queryjustin = "SELECT * FROM `cpProducts` as A " .
"left join `cpProductsValues` as B ON (A.id_product = B.id_product) " .
"left join `cpProductsFields` as C on (B.id_productfield=C.id_productfield) where (C.display='Artist' or C.display='format') and " .
"DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= A.created order by XXXXXX";
$resultjustin = mysql_query($queryjustin) or die(mysql_error());

I need to fill the XXXXXX with some rows from the B.value column, but only the rows where C.display=Artist.  Any ideas?

[Psycho]

I think there's an easy answer to your problem, but I'm not 100% clear on what you want. What are you expecting to happen to the rows where C.display != artist? Are you wanting those to stay in place or are they sorted by some other criteria.

How bout you show a few rows of sample data and how you would like it sorted.

[oliveralden]

Okay.  Here's three sample displays:

A Woman Under the Influence vhs
$ 6.00
VHS 1999/1974 M-/M-

Brian Eno Here Come the Warm Jets lp
$ 10.00
LP 1973 M-/M-

Byrds Preflyte lp
$ 10.00
LP 1973/1964 VG+/EX

Those are displays of three columns:
A.name
A.price
B.value where C.id_productfield = '7'

I'd like to sort by B.value where C.id_productfield = '2'.  The resulting display would be the same, but would sorted in alphabetical order by artist.  Which would look like this:

Byrds Preflyte lp
$ 10.00
LP 1973/1964 VG+/EX

A Woman Under the Influence vhs
$ 6.00
VHS 1999/1974 M-/M-

Brian Eno Here Come the Warm Jets lp
$ 10.00
LP 1973 M-/M-

The reason those displays would show up in that order is that B.value where C.id_productfield = '2' would equal Byrds, Cassavetes, and Eno, respectively.

Is that helpful?  Thanks for your time & any help you can give me.

[oliveralden]

Ooh, I should clarify.  I referred to the rows I want in two different ways above. 

Each time the B.values I want to sort by come up in the table, two things are true:
C.display=Artist
and
C.id_productfield=7

Again, thanks & hope that's helpful.